Question
Project Objective: The main goal of this project is to give students an opportunity to put the theory of database modelling, implementation, and application design
Project Objective:
The main goal of this project is to give students an opportunity to put the theory of database modelling, implementation, and application design into practice. The result will be a fully functioning database application.
It includes three main phases:
The first phase: Analysis (ERD).
The second phase: Design of the relational DB.
The third phase: Implementation of the relational DB.
Instructions of the project:
You can work on this project as a group (maximum 3 students).
Develop a proposal for the database management system project.
Groups should submit for approval their project ideas. This proposal should include:
A separate cover page indicating the title of your project, the full names of the group members (with student ID), the course number and course section.
A narrative description of the problem or opportunity being addressed.
Identification of the information needs
what information would help solve the problem or allow one to take advantage of the opportunity.
Initial list of entities (tables) that have been identified. This should come naturally from the above discussions.
Distribution of duties for the project. List the names of each group member and what their primary role.
Groups should not continue working on the project unless it has been approved by the instructor.
Project worth = 15 marks, will be distributed as following: Design the database following an ER model. (3 marks)
The ER should contain all necessary information such as entities, attributes, relationships, cardinalities etc.
Convert your ER diagram to a relational schema. (2 marks)
Underline all key attributes in your relations.
Specify all functional dependencies for each relation. (Mapping)
Use Oracle to create the normalized tables. (2 marks)
Populate your tables with 5 rows at least. (2 marks)
Design and implement at least 4 queries. (2 marks) As specified below
Data Retrieval Queries with Select (total of 4 Queries)
Where
Order By
Group By
Aggregate functions
Subquery
Join query
Note:
You are free to mix between all those clauses in one or more query, but you have to use them all so the total number of queries for Data Retrieval Queries with Select in the end will be 4.
Using select all (*) is NOT accepted.
Design two stored procedures as the following (4 marks)
Create a PARAMETER based SELECT QUERY stored procedure which return records based on parameters?
Create an UPDATE query based stored procedure
Each team should submit one report for each group about his/her project via the Blackboard contains the following:
Project title.
Group members information (name, id, email)
ER Diagram.
All schemas before and after normalization.
All SQL statements of:
Creating tables
Inserting data in tables
Queries.
Stored Procedures
Screenshots from oracle of all the tables after population and queries results.
Project Objective:
The main goal of this project is to give students an opportunity to put the theory of database modelling, implementation, and application design into practice. The result will be a fully functioning database application.
It includes three main phases:
The first phase: Analysis (ERD).
The second phase: Design of the relational DB.
The third phase: Implementation of the relational DB.
Instructions of the project:
You can work on this project as a group (maximum 3 students).
Develop a proposal for the database management system project.
Groups should submit for approval their project ideas. This proposal should include:
A separate cover page indicating the title of your project, the full names of the group members (with student ID), the course number and course section.
A narrative description of the problem or opportunity being addressed.
Identification of the information needs
what information would help solve the problem or allow one to take advantage of the opportunity.
Initial list of entities (tables) that have been identified. This should come naturally from the above discussions.
Distribution of duties for the project. List the names of each group member and what their primary role.
Groups should not continue working on the project unless it has been approved by the instructor.
Project worth = 15 marks, will be distributed as following: Design the database following an ER model. (3 marks)
The ER should contain all necessary information such as entities, attributes, relationships, cardinalities etc.
Convert your ER diagram to a relational schema. (2 marks)
Underline all key attributes in your relations.
Specify all functional dependencies for each relation. (Mapping)
Use Oracle to create the normalized tables. (2 marks)
Populate your tables with 5 rows at least. (2 marks)
Design and implement at least 4 queries. (2 marks) As specified below
Data Retrieval Queries with Select (total of 4 Queries)
Where
Order By
Group By
Aggregate functions
Subquery
Join query
Note:
You are free to mix between all those clauses in one or more query, but you have to use them all so the total number of queries for Data Retrieval Queries with Select in the end will be 4.
Using select all (*) is NOT accepted.
Design two stored procedures as the following (4 marks)
Create a PARAMETER based SELECT QUERY stored procedure which return records based on parameters?
Create an UPDATE query based stored procedure
Each team should submit one report for each group about his/her project via the Blackboard contains the following:
Project title.
Group members information (name, id, email)
ER Diagram.
All schemas before and after normalization.
All SQL statements of:
Creating tables
Inserting data in tables
Queries.
Stored Procedures
Screenshots from oracle of all the tables after population and queries results.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started