Computer sciences and Information technology:
Database Systems:
DATABASE DESIGN COURSWORK:
Perform a logical database design (i.e. data analysis, data design and normalisation) for the situation described below.
Requirements:
Consider the operation of a taxi booking system. Customers phone (and in the future may use the web) to book a taxi, giving pick up and drop off locations and a time. Assume that we wish to save details about customers for ease of bookings and future use, so recording their home address and contact details may be useful. The system then allocates a taxi to that booking. The system uses a number of firms to satisfy the bookings, the firms may have various numbers of taxis, some may be a one person operation, others may run a fleet of taxis.
You should consider both current and archival uses of the data in your solution. In your report you should note and justify any assumptions you make.
Write a report to present your work including the following sections:
1. Data Analysis
To identify correctly the conceptual data model (i.e., entities, relationships, and attributes) using either bottom-up data analysis or top-down data analysis.
2. Data Design
To use the correct structures supported by the DBMS (i.e., relations, attributes, and keys) to represent the entities, attributes and relationships of the conceptual data model.
3. Normalisation
To identify all functional dependencies of relations, exclude certain undesirable properties from database designs, and normalize the data design to produce a set of 3NF relations. To identify the primary key and/or foreign keys of all relations.
4. Entity Relationship Diagram
To draw an Entity Relationship (ER) diagram, including all the entities and their relationships.
5. Final design (list of relations and their attributes)
To produce a list of relations and their attributes using PostgreSQL. This should be in the form of output from PostgreSQL using dt and d tablename.