Version 1:
Database Design and Development
Assignment - Portfolio - TOP Hairdressing
Learning Outcome 1: Utilize an appropriate design tool to develop a relational database system for a significant problem.
Learning Outcome 2: Build a fully functional relational database system based on an existing system design.
Learning Outcome 3: Conduct comprehensive testing of the system against specified user and system requirements.
Scenario: TOP Hairdressing
You have been tasked with creating a report that includes the following components:
Q1: Analyze the hairdressing business for which you are developing the database system. Describe business rules and key data elements. Explain the importance of this data within the information system being developed. Discuss legal and ethical considerations related to data management within your specific topic.
Q2: Use the Oracle Data Modeler Tool to draw an Entity-Relationship Diagram (ERD) design, including at least 4 or 5 entities with attributes, cardinalities, and relationship names. Ensure the diagram is clear and readable.
Q3: List the relational table headings derived from your ERD. Clearly identify primary keys (underlined) and foreign keys (marked with *, or in italics). Ensure consistency with your ERD.
Q4: Create your database using MySQL or SQL Server, preserving your CREATE TABLE statement. Highlight any additional constraints applied. Ensure at least one table has a composite primary key. Define primary and foreign keys within the initial CREATE TABLE statements.
Q5: Populate all tables with data, aiming for a minimum of 8 or more rows per table. Display all data rows using SELECT * from;. Use meaningful test data, with tables at the "many" end of relationships containing more data.
SQL & QUERIES:
Q6-Q11: Develop 6 SQL queries demonstrating appropriate use of the BETWEEN logical operator, DISTINCT keyword, TO_CHAR function, aggregate function, GROUP BY command, and SUBQUERY with OUTER JOIN. Include a brief description of each query`s purpose, the SQL query itself, and the resulting output.
Q12: Devise a DELETE command to test foreign keys and referential integrity. Explain any encountered error messages.
Q13: Use an UPDATE command to test foreign keys. Interpret any error messages generated.
Q14: Construct an INSERT command to test primary key integrity. Explain any resulting error messages.
Version 2:
Database Design and Development
Assignment - Portfolio - TOP Hairdressing
Learning Outcome 1: Apply an appropriate design tool to create a relational database system addressing a substantial problem.
Learning Outcome 2: Develop a fully functional relational database system based on an existing system design.
Learning Outcome 3: Conduct system testing against user and system requirements.
Scenario: TOP Hairdressing
You have been given the task of compiling a report with the following components:
Q1: Analyze the hairdressing business and its database requirements. Describe business rules and essential data. Justify the significance of this data within the information system and discuss related legal and ethical considerations.
Q2: Use Oracle Data Modeler Tool to create an ERD design featuring at least 4 or 5 entities, including attributes, cardinalities, and relationship names. Ensure clarity and readability.
Q3: Define relational table headings based on your ERD design, highlighting primary keys (underlined) and foreign keys (marked with *, or in italics). Ensure consistency with your ERD.
Q4: Build your database using MySQL or SQL Server, ensuring to save your CREATE TABLE statement. Include additional constraints and ensure at least one table has a composite primary key. Define primary and foreign keys within the initial CREATE TABLE statements.
Q5: Populate all tables with data, aiming for a minimum of 8 rows per table. Use SELECT * from; to display all data rows. Include meaningful test data, with tables at the "many" end of relationships containing more data.
SQL & QUERIES:
Q6-Q11: Devise 6 SQL queries showcasing the appropriate use of the BETWEEN logical operator, DISTINCT keyword, TO_CHAR function, aggregate function, GROUP BY command, and SUBQUERY with OUTER JOIN. Provide a brief description of each query`s purpose, the SQL query, and the resulting output.
Q12: Create a DELETE command to test foreign keys and referential integrity. Explain any encountered error messages.
Q13: Utilize an UPDATE command to test foreign keys. Interpret any resulting error messages.
Q14: Develop an INSERT command to test primary key integrity. Explain any resulting error messages.
Version 3:
Database Design and Development
Assignment - Portfolio - TOP Hairdressing
Learning Outcome 1: Utilize a suitable design tool to design a relational database system for a significant problem.
Learning Outcome 2: Develop a fully operational relational database system based on an existing design.
Learning Outcome 3: Conduct thorough testing of the system against user and system requirements.
Scenario: TOP Hairdressing
Your assignment is to create a report encompassing the following elements:
Q1: Analyze the hairdressing business and its database needs. Describe business rules and critical data elements. Justify the importance of this data within the information system and discuss legal and ethical considerations.
Q2: Use the Oracle Data Modeler Tool to design an ERD comprising at least 4 or 5 entities, including attributes, cardinalities, and relationship names. Ensure the diagram is clear and understandable.
Q3: List relational table headings based on your ERD, clearly identifying primary keys (underlined) and foreign keys (marked with *, or in italics). Ensure consistency with your ERD.
Q4: Implement your database using MySQL or SQL Server, preserving your CREATE TABLE statement. Specify additional constraints and ensure at least one table has a composite primary key. Define primary and foreign keys within the initial CREATE TABLE statements.
Q5: Populate all tables with meaningful data, aiming for a minimum of 8 rows per table. Display all data rows using SELECT * from;. Use appropriate test data, with tables at the "many" end of relationships containing more data.
SQL & QUERIES:
Q6-Q11: Create 6 SQL queries demonstrating the appropriate use of the BETWEEN logical operator, DISTINCT keyword, TO_CHAR function, aggregate function, GROUP BY command, and SUBQUERY with OUTER JOIN. Include a brief description of each query`s purpose, the SQL query, and the resulting output.
Q12: Devise a DELETE command to test foreign keys and referential integrity. Interpret any error messages encountered.
Q13: Utilize an UPDATE command to test foreign keys. Explain the meaning of any resulting error messages.
Q14: Construct an INSERT command to test primary key integrity. Explain any resulting error messages.
