Code- COIT20247 Assignment Help
Subject- IT Assignment Help
Instruction:
- Write your answers in a MS Word document. Submit your answer document via the moodle website link (With the link name – “Assignment 3”). Your answer document should contain a cover page with your name and student.
This assessment material contains Part A, B, and C. You should attempt all questions in all parts. The total marks available in the alternative SE are 35.
The questions in Part A and Part B are based on a case study in the last page of this document.
Part A. (15 Marks) Question 1. (8 marks)
Draw an E-R modelling diagram on the case study. Your diagram should clearly display necessary entities and attributes, relationships, cardinalities and primary keys. (You can use MS Visio or MS Word to draw the diagram).
Question 2. (7 marks)
Convert your ER model in Question 1 into a set of relations that satisfy Third Normal Form (3NF). You do not need to justify that they are in 3NF at this stage. You do not need to show sample data. Just show your relations. You should write your relations in the format shown below:
Student (Student ID, Student Name, Date Of Birth)
Enrolment (Enrolment ID, StudentID, Date Of Enrolment)
Foreign key (StudentID) references Student
Part B. (10 Marks, each of 2 marks)
The following query questions are based on the case study in this document. Assume a database is correctly implemented after your E-R modelling. Answer the following SQL query questions by writing your SQL statement. (Note: just write SQL query statements, no need to provide a MS Access database. You can build a MS Access database for your test purpose if you would like.)
Question 1. List all members in Queensland and NSW who have never ordered any liquor.
Question 2. List all orders that members have placed for the purchase of a kind of beer with the brand name of “Tooheys”(which includes Tooheys Red and Tooheys Original and other series of beer under the brand Tooheys).
Question 3. List the brand name and brewer of liquors that have two or more ratings.
Question 4. Find out the details of most expensive wine. The details include wine’s name, brewer, price, rating points and comments.
Question 5. Find out the storage locations where the storage quantity of all brand of spirits is less than 150 bottles.
Part C. (10 Marks, each of 2 marks)
Question 1. In the context of database, use an example to explain the concept of insertion anomaly.
Question 2. Use your own language and an example to explain the concept of transitive dependency.
Question 3. In the database design, if the relations are not normalized properly, what consequence will be caused?
Question 4. Briefly describe the main goals that a distributed database tries to achieve.
Question 5. Briefly discuss each of the typical backup facilities in a modern database management system.
Case Study: Queensland Liquor National online trader database
Queensland Liquor National, better known as QLN, owns an entertainment club named as Queensland Liquor Club (QLC) and an online business trading a variety of liquors ranging from French prestigious champagnes to native Tasmanian beers. The aim of QLN is to provide premium quality liquors in affordable prices for its club members and in the meantime they also regularly publish e-newsletters on the most recent review and rating of various liquors ranging from old Scottish whisky to the 21st century home-brewed ale, which have been popular among liquor lovers. The CEO of QLN needs a relational database used as the most critical backend software component in the enterprise software system to run the online business so that the relevant information will be recorded appropriately and processed efficiently. The proposed database needs to track information including liquors, online sale record, deliveries, stock, rating and members.
QLN keeps a large pool of its customers. Each customer must register as a member of QLC in order to make an online purchase of liquors provided by QLN. For each member, QLN wishes to record their unique member number (as identification number), name, a complete address, age, registration date, and contact phone. They need to be able to record multiple phone numbers and phone types for each member. The valid type for each member is either “private” or “business”. QLN does keep some members on record who have never made an online purchase. Sometimes members make general enquiries but never actually purchase a bottle of liquor mainly due to their budget constraint, despite their enjoyment of drinking liquor. QLN keeps their details in the database system so that they can follow up with these members at late date to see if they have improved their income and budget situation.
The online sale business is very similar to today’s conventional eCommerce on internet. The company runs an internet website with a backend database system in a server computer. The front- end webpages of the website have some common graphical user interface (GUI) components and images to provide online shopping functionality and introducing features of liquors. To purchase liquors, the registered customers, i.e. members will browse the webpage, drag liquor icons to the shopping cart, fill in or select the quantities they wish to buy, and finally click “Complete” and “Payment” buttons. Each online order will be automatically assigned to a single staff member who will check and process the order. Staff details (including name and phone) must be recorded in the system. Not all staff will process an order but majority of staff may process many orders. Normally the liquor ordered by members will be delivered to the member address with a low shipment cost in three days. The delivery information should be noted with the data attributes like the delivery company name and phone, cost, and shipment date. After processing, the status of an order should be assigned to one of possible values – processed, paid, delivered, and cancelled, and accordingly the relevant actions have been taken.
For an efficient management and a faster search/query, QLN wishes to record the details on the liquors they sell online. These details include liquor identification number, brand name or description, brewer name, production date, alcohol content (in terms of percentage), unit price, a brand icon (image) and storage locations. QLN has a number of warehouses in the state-wide locations where various liquors are stocked under the strict health and safety standard. Each warehouse should have an identification number. The address of warehouse and contact phone must be recorded, in addition to the quantity of each kind of liquor in the stock either in the unit of bottle or box. QLN has made the arrangement that different warehouses can stock full-range brand liquors. This is helpful for a rapid delivery. In addition, all liquors that QLN sell are placed into three distinctive categories: beer, wine and spirits. The beer style (a combination description of aroma, flavour and strength) must be noted for each kind of beer. For a wine, the grape species used tobrew is required to record; while for spirits, the category description (for example, gin, vodka and Lowland whisky etc) is an essential data item.
Finally, the database also records and updates the rating information for each kind of liquor. The rating was conducted by some internationally recognized organizations like Beverage Testing Institute (USA), based on their thorough research, lab testing and numerous consumer surveys. The rating information should contain the content such as the name of rating organization, rating value or points, review comments and date. It should be pointed a liquor could be rated with different organizations.
[Hint: The entities and associative entities should include: Member, Staff, Order, Delivery Company, Order Details, Liquor, Beer, Wine, Sprits, Rating, Stock and Warehouse.]
Leave A Comment