Assignment 2 Specification: The Assignment Must Be Submitted

Assignment 2 Specificationthe Assignment Must Be Submittedelectronical

The assignment must be submitted electronically via the online assignment submission link on the study desk. Instructions will be provided on the course study desk. If you are unable to complete the whole assignment, submit what you have done rather than nothing. You must use the official USQ data modelling and normalization methodology. This methodology is based on Clive Finkelstein’s techniques (SR 2.1 and SR 2.2) and all the examples in the lectures, study book, and tutorials use this methodology.

If you do not use the USQ methodology, you will probably be awarded a mark of zero. You might wish to use a word processor or a drawing tool. In either case, you must adapt the drawing to conform to the USQ methodology. This assignment covers much of the same ground as the exam and will provide you with a strong indicator of your level of preparedness for the exam. Most of the questions are of examination standard.

Paper For Above instruction

Section A: Data Modelling (40 marks)

A local pharmacy store in Wollongong aims to develop an online system to track their product inventory and prescription records for regulatory compliance. The core requirement involves designing a data model to identify entities, attributes, and business rules. The primary entity is ITEM, representing all pharmacy products, with attributes including item code, shelf, location, and quantity.

Order details such as order date, order quantity, and special instructions are stored for each item ordered. Patients are identified by id, name, date of birth, contact number, and next of kin, with a possibility of multiple orders per patient. Some orders originate from casual customers without patient association.

Items can be of two types: medical and non-medical. Over 95% are medical items. Non-medical items have an additional promotion period attribute, valid only during promotions. Medical items are further classified into doctor-prescription, over-the-counter, and no-prescription items. Prescription records are maintained accordingly, with relationships between doctors, pharmacists, and items, including prescription date and special instructions. Doctor prescribers are associated with multiple prescription items, each with their details (name, specialty, registration number). Over-the-counter items are prescribed by store pharmacists during consultations, and multiple pharmacists can prescribe different OTC items, necessitating relationship attributes.

Pharmacists and general staff are categories of personnel. Staff attributes include name, date of birth, joined date, qualifications, and level. Pharmacists additionally store registration numbers.

a) ER Diagram: Develop an ER diagram using the USQ Finkelstein methodology, including all entities, relationships, cardinalities, optionalities, and intersection entities.

b) List of Relations: From the ER diagram, produce complete relation schemas with primary and foreign keys, incorporating all mentioned attributes using the Finkelstein methodology.

c) SQL CREATE TABLE Statement: Write a CREATE TABLE statement for the ITEM relation, including primary and foreign key constraints, conforming to business rules.

Section B: Normalization (20 marks)

Normalize the following unstructured relation of a flight reservation system into Third Normal Form (3NF), following the USQ Finkelstein methodology. The relation contains details on flights, aircraft, crew, bookings, and passengers, with nested and repeating groups that need normalization.

Include considerations such as composite keys, derived attributes, resolving partial and transitive dependencies. Ensure all relations are correctly named, with primary and foreign keys, and all attributes are present. All relations should be free of repeating groups and redundant data structures.

Section C: SQL Queries (40 marks)

Utilizing the Car Hire database provided, write SQL queries to fulfill the specified data retrieval tasks, capturing the output and providing screenshots as required.

Tasks include retrieving car booking details based on mileage and reservation staff, listing customers without bookings, showing booking statuses and costs, calculating average miles per car, summing rental income, and more, with filtering constraints. Each query must be syntactically correct and optimized.

References

• Finkelstein, C. (1982). Data models and normalization. Australian Computer Journal, 14(1), 16–27.
• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.
• Codd, E. F. (1970). A Relational Model of Data for Large Shared Data Banks. Communications of the ACM, 13(6), 377–387.
• Connolly, T., & Begg, C. (2014). Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.
• Date, C. J. (2004). An Introduction to Database Systems. Pearson Education.
• Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts. McGraw-Hill.
• Harrington, J. L. (2016). Relational Database Design and Implementation. Morgan Kaufmann.
• Raghu Ramakrishnan & Johannes Gehrke. (2003). Database Management Systems. McGraw-Hill.
• Abraham, S., & Sharma, N. (2017). Normalization and Database Design. International Journal of Computer Applications.
• O'Neil, P., & O'Neil, E. (2014). Database: Principles, Programming, and Practice. Cengage Learning.