Defining the project:
Project Title ( i select airline system)
Problem Description
Scope of the system (main features)
Preliminary data requirement
Preliminary business rules
Phase 1 (design):
1- System Description
The following must be revised and refined by students (from previous report):
The problem description of the system
Data requirements
The business rules of the system
Furthermore, students must provide the following details:
What are the outputs of the system? What are the queries, reports, calculations which the system will provide to its users? Must be comprehensive and concise.
2- ER Diagram
Given the detailed problem description, data requirements, and business rules of the system, students will draw the conceptual ER schema model.
Students must illustrate on the diagram each entity type, relationship, cardinality ratio, and participation constraints (total or partial) of entities in relationships.
3- Design Justification
Students must demonstrate how the business rules which they have stated has affected their design decisions.
4- ER-to-schema-mapping
Mapping the ER diagram into a DB schema diagram. Following the steps of the algorithm in chapter9. Illustrating on the diagram the following:
Table names
Referential integrity constraints
Primary keys and Foreign keys
Phase 2 (Normalization)
In project design phase I, students have created their ER diagrams and mapped them into conceptual schemas. They have also received concise feedback on their design.
In project design phase II, students will be asked to refine their ER diagrams and ER to schema mapping based on instructors feedback. Then students will have to apply normalization process to their schemas. Transform their DB schema up to third normal form.
Phase III (Implementation + final Report)
Students must create the DB schema of their system after applying all necessary corrections on their design from phase II.
The outcome of this final phase will be:
1- An implemented DB system.
2- A final report. The document template must be provided to them.
The final report document consists of two parts. The first part includes the DB design which they have already designed. The second part of the report includes the implementation details.
IMPLEMENTATION STEPS
1-Creating tables
Students must create tables in SQL and define the Built-in Relational Model Constraints which must include the following:
1- Primary keys
2- Referential integrity constraints
3- Entity constraints
4- Business rules (from the relevant section in the report)
2- Populate tables with data
Students must populate each table with at least 10 rows. Students must provide a list of all the data of their tables in the APPENDIX at the end of the report. Using SQL queries to provide the list.
3- Implementing queries
Students must implement five queries. The queries should include at least one nested query, at least one query using nested aggregate functions, and at least one query using SQL operations (IN , EXISTS, UNION, INTERSECT). ALL queries must be complex queries.
0 comments