Database Project Phases and Instructions
Phase 1: Select an Organization
1. Form a team of 2-3 members.
2. Select an organization on campus or a commercial enterprise (actual or fictitious) with a need for a large database.
3. Describe the enterprise and its need for a database. Explain how the database will be useful for the organization and what sort of information will be stored in it. Give the description as the DBA for the system, not as the owner or manager of the organization. (You may refer to the Mountain View Case Study, and also the Pine Valley Furniture Company description given in chapter 1 and 2 of your books).
Submission
Create and submit a word document (one submission per team) with the following in it:
1. Title for your project.
2. Names of all teams members.
3. Description of the organization, and the dataset as described above. (This needs to be more than one paragraph - between 1 to 2 pages long).
Phase 2-1: Data Analysis
Based on your description of the organization or process defined in Phase 1, model the different entities for which you will be capturing and storing information. Your data model,
Must contain at least 7 – 8 entities
Must contain at least 7 – 8 relationships between the various entities
Each entity must have appropriate list of attributes
Incorporate and identify different types of attributes (e.g. make name and address as composite attributes)
For this phase, please submit a list of the entities with their attributes, along with the data definitions for the entities. In addition, also write a set of business rules that will formalize the relationships that exist between the entities.
Phase 2 - 2: Design an E-R Model
Draw an E-R diagram depicting entity types, attributes, identifiers, relationships, minimum/maximum cardinalities, etc. define by you in Phase 2-1.
Since the entire project of creating and running your database depends on the E-R model, you should spend time designing it. Use a drawing tool on a computer for the E-R diagram.
Phase 3: Logical Schema Design
1. Convert the approved ER Model from Phase 2-2 to its logical schema.
2. Identify the normal form for each relation within the schema.
3. For any relation not in 3NF, normalize to 3NF.
Submit your final logical schema design. Include the first two phases along with the logical schema. Please follow a report format. You are building your final report for submission as we go along.
Phase 4: Implementation
1. Implement your relational model using MySQL relational database system. Create all the tables in MySQL workbench.
2. Provide SQL insert statements that add sample data into the tables. Add at least 7 to 8 records in each table. If you insert data using MySQL workbench, then you don't have to write the insert statements.
3. Write 5 queries in English language, and then convert them in terms of SQL select statements. Provide results (screen shots) from executing those queries. Ensure that your queries illustrate a full range of select statements including joins, sub queries, aggregate functions, etc.
Phase 5: Complete Report
Your final report will contain all four phases combined.
Create a title page with your project title, and the names of all team members.
Please make sure that the report contains titles for all sections, and figure captions for all figures.
Also include a brief description of the DBMS and tools used for modeling and implementing the project.
1. Form a team of 2-3 members.
2. Select an organization on campus or a commercial enterprise (actual or fictitious) with a need for a large database.
3. Describe the enterprise and its need for a database. Explain how the database will be useful for the organization and what sort of information will be stored in it. Give the description as the DBA for the system, not as the owner or manager of the organization. (You may refer to the Mountain View Case Study, and also the Pine Valley Furniture Company description given in chapter 1 and 2 of your books).
Submission
Create and submit a word document (one submission per team) with the following in it:
1. Title for your project.
2. Names of all teams members.
3. Description of the organization, and the dataset as described above. (This needs to be more than one paragraph - between 1 to 2 pages long).
Phase 2-1: Data Analysis
Based on your description of the organization or process defined in Phase 1, model the different entities for which you will be capturing and storing information. Your data model,
Must contain at least 7 – 8 entities
Must contain at least 7 – 8 relationships between the various entities
Each entity must have appropriate list of attributes
Incorporate and identify different types of attributes (e.g. make name and address as composite attributes)
For this phase, please submit a list of the entities with their attributes, along with the data definitions for the entities. In addition, also write a set of business rules that will formalize the relationships that exist between the entities.
Phase 2 - 2: Design an E-R Model
Draw an E-R diagram depicting entity types, attributes, identifiers, relationships, minimum/maximum cardinalities, etc. define by you in Phase 2-1.
Since the entire project of creating and running your database depends on the E-R model, you should spend time designing it. Use a drawing tool on a computer for the E-R diagram.
Phase 3: Logical Schema Design
1. Convert the approved ER Model from Phase 2-2 to its logical schema.
2. Identify the normal form for each relation within the schema.
3. For any relation not in 3NF, normalize to 3NF.
Submit your final logical schema design. Include the first two phases along with the logical schema. Please follow a report format. You are building your final report for submission as we go along.
Phase 4: Implementation
1. Implement your relational model using MySQL relational database system. Create all the tables in MySQL workbench.
2. Provide SQL insert statements that add sample data into the tables. Add at least 7 to 8 records in each table. If you insert data using MySQL workbench, then you don't have to write the insert statements.
3. Write 5 queries in English language, and then convert them in terms of SQL select statements. Provide results (screen shots) from executing those queries. Ensure that your queries illustrate a full range of select statements including joins, sub queries, aggregate functions, etc.
Phase 5: Complete Report
Your final report will contain all four phases combined.
Create a title page with your project title, and the names of all team members.
Please make sure that the report contains titles for all sections, and figure captions for all figures.
Also include a brief description of the DBMS and tools used for modeling and implementing the project.