Identify all entities and relationships in the case study and develop a set of business rules. You should follow the syntax given below. Two rules must describe each relationship, one in each direction.
Each/A/An ENTITY_1 May/Must Relationship_Verb_Phrase number ENTITY_2
Based on the business rules developed in Task 1, construct a logical ERD for the case study using the Visual Paradigm tool.
- Identify all attributes in each entity, including all primary and foreign keys (transform any composite and multivalued attributes according to the rules of the relational model).
- Name all relationships (i.e. you must use verb phrases on both sides of each relationship)
- Identify the Cardinality and Participation for each relationship.
- Resolve all many to many (M:N) relationships.
- Describe any assumptions you have made in a text note on the diagram.
- You don’t need to identify data types in your ERD.
- You don’t need to answer task 2 questions separately. You identify them on the diagram.
In this part, you should develop a database based on your logical ERD developed in Part A. You must populate the tables with realistic sample data.
Create the tables in Oracle.1 Write SQL scripts defining each table. The table definitions should include
- All attributes with appropriate data types
- All appropriate constraints such as primary key, foreign keys, and check statements (CHECK constraints).
- All constraints must be given names. Naming standards must be used.
Insert a small sample of realistic test data (5 –10 rows minimum) into each table. All test data must be meaningful to demonstrate your understanding of the data.
After creating all your tables (Task 3) and inserting data (Task 4), run the SQL SELECT statement below:
SELECT table_name FROM user_tables;
The statement will retrieve a list of all tables that exist in your Oracle account (i.e., all tables you created). Include the results of the SELECT statement in your assignment.
You must also include the following for each table in your database:
- SQL table creation script
- SQL insert script (used for inserting sample data)
- Sample data – you must run “SELECT * FROM table_name” (where table_name is the name of the table) for each of your tables in Task 3 and include results of SELECT statements for each table.
Identify five data retrieval requirements that would be used by the business described in the case study. For each requirement you have identified, construct a SQL query needed to display the data. So, you must construct five SQL queries in total. The queries must include a combination of the following SQL features:
- Use of restriction (WHERE clause), Arithmetic expressions, Concatenation of columns
- Use of comparison and logical operators
- Multiple tables join (e.g., Equijoin, Outer Join, and Self-join)
- Group functions (e.g., COUNT, SUM, AVG, MAX, MIN) and clauses (e.g., ORDER BY, GROUP BY, HAVING)
- You may combine the preceding features in a single query. The design of each query should demonstrate your knowledge and application of SQL content covered in the course and understanding of the correct usage of the various features and clauses of SQL. A simple ‘select * from table_name’ will fetch you a mark of zero.
- There should be no more than one query that involves a single table.
- An example of a query is given on the next page.
|Staff No||Staff Name||Qualification|
For Part C, you must include the following (as shown above) for each SQL query statement:
- Description of the purpose of the query.
- SQL SELECT query
- The result of the query
|Part C Construct SQL Queries|
|Part C – Task 5||Construct five SQL Queries: Each queryRetrieves meaningful information to support the data and transaction requirements of the case studyeach query is distinct and significantly different from one anotherincludes a description of the purpose of the queryDemonstrates knowledge and application of SQL content covered in the course (covers important SQL features, clauses, and functions)Use of restriction (WHERE clause), Arithmetic expressions, Concatenation of columnsUse of comparison and logical operatorsMultiple table join (e.g. Equijoin, Outer-join, and Self- join)Group functions (e.g., COUNT, SUM, AVG, MAX, MIN) and clauses (e.g., ORDER BY, GROUP BY, HAVING)includes results (output rows) Note:You may combine some of the preceding features in a single query. The design of each query should demonstrate your understanding of the correct usage of the various features and clauses of SQL.A simple ‘select * from table_name’ will fetch you a zero mark.There should be no more than one query that involves a single table||30|
|Part C Total||30|
|Total of Parts A, B, and C||100|