In this Project, each group of students is required to choose one of the following businesses and understand the database requirements based on the nature of its business. They need to use their understanding of the nature of the business to design a database and then use SQL to create the tables, and populate them with suitable data.
Each group needs to deliver a status report of what they have finished/are doing on a weekly basis. Members of each group need to use the following to come up with a good database design and implementation:
• Good research and analytical skills: when they search for similar businesses and find their database requirements.
• Good designing skills using Barker’s notation: When they design an ERD for the chosen business.
• Good SQL technical skills: When they write the SQL statements based on the project requirements
• Good writing skills: when they write the required sections in their report
The database development task can be related to any one of the following businesses:
The designed ERD must demonstrate the students’ understanding of both the chosen business and the components of an ERD diagram.
The created report should include all the sections listed in the next page.
PART 1: General information about the Report:
1. What should I include in the report:
Students are required to include the following sections:
Section 1: Introduction
Section 2: Overview of the chosen company and the adopted business rules.
Section 3: The ERD Diagram
Section 4: The Relational Model
Section 5: SQL statements (DDL & Inserting Data)
Section 6: SQL statements (Queries)
Section 7: Conclusion
2. What should I include in each one of these section?
Following is a summary of what students need to include in each section:
Section 1: Introduction (5 Marks)
Students need to write general information about the project. This may include information about the team members. It can also include an overview of the sections in the report, and a summary of what is covered in each section.
Section 2: Overview of the chosen company (5 Marks)
In this section, Students need to provide an overview of the company. This may include: What is the nature of business? What kinds of data they need to keep and manage? What are the business rules they decided to implement.
Section 3: The ERD Diagram (20 Marks)
In this section, Students need to design the ER Diagram. This should reflect the following general information:
The primary purpose of the database is to enable the management of your selected business to maintain information about the following:
• Orders/Service request
• And any other information that you may suggest.
The created ERD should include the following elements:
1. A minimum of 6 entities (events or objects) that describe the business environment.
2. The attributes or facts about each of the entities identified. (Make sure you include ALL important attributes)
3. The relationship that exists between the entities (1:1, 1:M or M:M). (Make sure to resolve any M:M relationship.
4. Suitable names for the relationships in your ERD.
5. The Primary Key for each table in your model.
6. The Foreign Key(s) where applicable. (Use appropriate constraints)
7. Any assumptions you made in modeling the ERD (This must be consistent with the business rules listed in section 2.
ERD should be modeled using Barker’s Notation. You are encouraged to draw the ERD using your Oracle SQL Developer Data Modeler tool.
Section 4: The Relational Model (5 Marks)
Convert the ERD into Relational Model Diagram (with primary and foreign keys identified correctly. Make sure you convert ALL entities into relational tables.
Section 5: SQL statements (DDL & Inserting Data) (20 Marks)
First. In this section, you need to use http://apex.oracle.com to create an Oracle database according to the relational model you have created in Section_4. To create this database, you are requested to do the following:
1. Write the required DDL statements to create the database tables.
2. Choose a suitable data type for each one of your fields
3. Create the needed Primary Key constraints
4. Create the needed FK constraints
5. Show your understanding of using the following constraints (NOT NULL, CHECK and UNIQUE) (3 Marks)
6. Save the DDL statement in a text document using Notepad.
Second. Populate the database tables with a reasonable number of records
Provide screen captures as evidence of completing the task.
Section 6: SQL statements (Queries) (30 Marks)
In this section, students are requested to write a minimum of 10 different queries. To do that, students need to stick to the following guidelines:
• For each one of the queries, explain what the query does by writing a question that the query should answer. The question is supposed to be logical and meaningful. (1Marks)
• Write an SQL statement that can be used to give an answer to the chosen question. (1Marks)
• The 10 different queries should include the use of the following: (1 Marks)
1. Arithmetic Operators (1 Mark)
2. Distinct (1 Mark)
3. Concatenation (1 Mark)
4. Comparison operators. (1 Mark)
5. BETWEEN … AND (1 Mark)
6. AND (1 Mark)
7. OR (1 Mark)
8. NOT (1 Mark)
9. IN (1 Mark)
10. LIKE (1 Mark)
Provide screen captures as evidence of completing the tasks.
Section 7: Conclusion
Students can use this section to summarize the outcomes of their project and give some details on the lessons they learnt and their experiences in designing an ERD, creating the relational model, building the database tables, populating the DB tables, and using different kinds of queries.
PART 2: Project Demo – Individual Q and A (15 Marks)
• Each individual is required to demonstrate her understanding and contribution to the project during an interview.
• During the interview, each team member will be asked a number of question (Note: questions can be based on the part of the project they were responsible for as well as the parts that other team members were responsible for).
CIS 1303 – Data and Information Management – AT3 ERD Rubric
Task # 0 4 8 12 16 20 Marks Awarded
ERD Model is inconsistent with the scenario selected. ERD missing key entities and relationships. Missing relationship degree, cardinalities and keys documentation.
Very limited attributes identified. Demonstrate No understanding of the use of datatype and field size in a DB.
None of the assumptions made are reflected in the design.
Model is inconsistent with the scenario selected. Model demonstrates poor ERD design skills. Most entities/ relationships/relationship degree/cardinalities and keys documentation not complete. Missing obvious attributes. Demonstrate basic understanding of the use of datatype and field size in a DB. Only one of the assumptions made is reflected in the design.
Model is not fully consistent the scenario selected. Model demonstrates average ERD design skills. Missing some entities/ relationships/ relationship degree / cardinalities or keys documentation Missing some attributes. Demonstrate average understanding of the use of datatype and field size in a DB. Few of the assumptions made are reflected in the design.
Model is consistent with the scenario selected. Overall, model demonstrates good ERD design skills. Missing few entities/ relationships/ relationship degree / cardinalities or keys documentation. Missing few attributes but attributes listed are realistic. Demonstrate good understanding of the use of datatype and field size in a DB. Some of assumptions made are reasonable, and are reflected in the design.
Model is consistent with the scenario selected. Model demonstrates very good ERD design skills. Overall, entities and relationships are modeled correctly with proper relationship degree, cardinalities and all keys included and appropriately documented. Almost all attributes identified are correct and complete. Demonstrate very good understanding of the use of datatype and field size in a DB. Majority of assumptions made are reasonable, and are reflected in the design.
Model is consistent with & fully represents the scenario selected. Model demonstrates
Excellent ERD design skills. Entities and relationships are modeled correctly with proper relationship degree, cardinalities and all keys included and appropriately documented. All attributes identified are correct, realistic and complete. Demonstrate excellent understanding of the use of datatype and field size in a DB. All assumptions made are reasonable, add value and are reflected in the design.
Marking Scheme – 1 (Report Rubrics)
Name: Student ID:
Item Information Marks available Marks Awarded
Section 1: Project overview • General overview of the project
• Members of the team
• A List of the sections
• A brief about each one of the sections 5
Section 2: Company overview • What is the nature of business
• What kinds of data they need to keep and manage?
• What are the business rules they decided to implement 5
Section 3: The ERD Diagram • A minimum of 6 entities
• The attributes.
• The relationship.
• Suitable names for the relationships.
• The Primary Keys.
• The Foreign Key(s)
• Any assumptions you made in modeling the ERD. 20
Section 4: Relational Model • Convert the ERD into Relational Model Diagram 5
Section 5: DDL & Inserting Data
• DDL statements to create the database tables.
• Suitable data types
• The needed primary key constraints
• The needed FK constraints
• Not null constraint
• Check constraint
• Populate the database tables
• Screen captures submitted as required. 20
Section 6: SQL Statements (Queries) • A question that the query should answer.
• A SQL statement to answer the chosen question.
• 10 different queries with a variety of operators
• Screen captures submitted as required. 30
Section 7: Project Demo/ Q and A No Answer Poor Good Excellent 15
Q1. 0 1-2 3-4 5
Q2. 0 1-2 3-4 5
Q3. 0 1-2 3-4 5