CPTR5600 Project Assignment Part A

Find a group of five people. Each group choose a project below for the design work. The part A due date should be June 19. Then you will proceed your part B work to implement.

Your part A should include the description of your design, the printed E-R diagram, the scheme of the database. And list the name and ID of students in your group.

  1. Design a database for a world-wide package delivery company (e.g., DHL or FedEX). The database must be able to keep track of customers (who ship items) and customers (who receive items); some customers may do both. Each package must be identifiable and trackable, so the database must be able to store the location of the package and its history of locations. Locations include trucks, planes, airports, and warehouses. Your design should include an E-R diagram, a set of relational schemas, and a list of constraints, including primary-key and foreign-key constraints.

2. A dentist’s office needs to keep information about patients, the number of visits they make to the office, work that must be performed, procedures performed during visits, charges and payments for treatment, and laboratory supplies and services. Assume there is only one dentist, so there is no need to store information about the dentist in the database. There are several hundred patients. Patients make many visits, and the database should store information about the services performed during each visit, and the charges for each of the services. There is a standard list of charges, keep outside the database. The office uses three dental laboratories that provide supplies and services, such as fabricating dentures. Draw a complete E-R Diagram for the implementation.

3.Our department needs to conduct assessment for certain courses each semester. The assessment sections for computer science include CPTR1100, CPTR2100, and CPTR3100. The assessment instrument for each section is a set of multiple choice questions. We expect your help to design a database system to automate the entire procedure. The system will need a test bank to store the test problems (instruments), It needs an engine to draw different versions of test each time given. Preferring the student can do on line assessment, i.e., answer the questions on line, graded on line, and generate the results automatically. Also, the system needs to keep the records of the assessment each time. Finally, based on the assessment result, the system needs to analyze the result and produce an appropriate report.

4.A record keeping system for a car insurance company. The customers of the insurance company own one or more cars each. Each car has associated with it zero to any number of recorded accidents. Each insurance policy covers one or more cars, and has one or more premium payments associated with it. Each payment is for a particular period of time, and has an associated due date, and the date when the payment was received.