Version 1:

Assignment - Portfolio - TOP Hairdressing

Learning Outcome 1: Utilize an appropriate design tool to create a relational database system addressing a significant problem.

Learning Outcome 2: Develop a fully operational relational database system based on an existing design.

Learning Outcome 3: Conduct system testing against user and system requirements.

Scenario: TOP Hairdressing

You have been assigned the task to produce a report with the following components:

Question 1: Analyze the hairdressing business for which you are designing the database system. Define the business rules and key data involved. Justify the importance of this data in the context of the information system being developed. Discuss any legal and ethical considerations related to managing this data within your specific topic.

Question 2: Utilize the Oracle Data Modeler Tool to create an Entity-Relationship Diagram (ERD) design. Aim for at least 4 or 5 entities, including attributes, cardinalities, and relationship names. Ensure the diagram is clear and understandable.

Question 3: Outline the relational table headings based on your ERD design. Clearly indicate primary keys (underlined) and foreign keys (marked with *, or in italics). Ensure consistency between your tables and ERD.

Question 4: Employ MySQL or SQL Server to create your database, preserving the CREATE TABLE statement. Highlight any additional constraints applied. Incorporate at least one table with a composite primary key. Ensure primary and foreign keys are defined within the CREATE TABLE statement, without relying on ALTER TABLE afterward.

Question 5: Populate all tables with data, aiming for a minimum of 8 rows per table. Display all rows of data using SELECT * from;. Use meaningful test data, focusing on more extensive data in tables at the "many" end of relationships.

SQL & QUERIES:

Question 6: Demonstrate the use of the BETWEEN logical operator in a SQL query.

Question 7: Implement the DISTINCT keyword within a query.

Question 8: Utilize the TO_CHAR function effectively.

Question 9: Incorporate an aggregate function within the SELECT list.

Question 10: Implement a GROUP BY command.

Question 11: Create a SUBQUERY, involving data from multiple tables, including the use of an OUTER JOIN.

Question 12: Develop a DELETE command to test foreign keys, checking referential integrity. Explain any error messages encountered.

Question 13: Use an UPDATE command to test foreign keys. Interpret any error messages generated.

Question 14: Construct an INSERT command to test primary key integrity. Interpret error messages as needed.

Ensure each query includes:

• A brief explanation of its purpose.
• The SQL query itself.
• The resulting output.

Version 2:

Assignment - Portfolio - TOP Hairdressing

Learning Outcome 1: Apply an appropriate design tool to craft a relational database system for a significant problem.

Learning Outcome 2: Develop a functional relational database system based on a predefined design.

Learning Outcome 3: Conduct thorough system testing against specified user and system requirements.

Scenario: TOP Hairdressing

Your task is to compile a report comprising the following components:

Question 1: Analyze the hairdressing business and its specific database needs. Define business rules and key data elements. Justify the importance of this data within the context of the information system. Discuss legal and ethical considerations relevant to managing this data.

Question 2: Utilize Oracle Data Modeler Tool to create an ERD design with a minimum of 4 or 5 entities, including attributes, cardinalities, and relationship names. Ensure clarity and readability of the diagram.

Question 3: Define relational table headings derived from your ERD. Clearly indicate primary keys (underlined) and foreign keys (marked with *, or in italics). Ensure alignment with your ERD.

Question 4: Use MySQL or SQL Server to create your database with appropriate CREATE TABLE statements. Highlight any additional constraints applied. Include at least one table with a composite primary key. Ensure primary and foreign keys are defined within the CREATE TABLE statements.

Question 5: Populate all tables with meaningful data, aiming for a minimum of 8 rows per table. Display all data rows using SELECT * from;. Use relevant test data, with a focus on tables at the "many" end of relationships.

SQL & QUERIES:

Questions 6-11: Devise SQL queries demonstrating appropriate usage of the BETWEEN operator, DISTINCT keyword, TO_CHAR function, aggregate functions, GROUP BY command, and SUBQUERY with OUTER JOIN.

Question 12: Construct a DELETE command to test foreign key constraints and referential integrity. Provide an interpretation of any encountered error messages.

Question 13: Use an UPDATE command to test foreign key constraints. Explain the meaning of error messages generated.

Question 14: Develop an INSERT command to test primary key constraints. Interpret any error messages.

Ensure each query includes:

• A concise description of its purpose.
• The SQL query.
• The resulting output.

Version 3:

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 a predefined design.

Learning Outcome 3: Test the system comprehensively against user and system requirements.

Scenario: TOP Hairdressing

Your task is to produce a comprehensive report containing the following elements:

Question 1: Conduct a detailed analysis of the hairdressing business, outlining business rules and critical data. Justify the importance of this data within the context of the information system. Discuss legal and ethical considerations related to data management in your specific domain.

Question 2: Utilize the Oracle Data Modeler Tool to construct an ERD design featuring a minimum of 4 or 5 entities, complete with attributes, cardinalities, and relationship names. Ensure the ERD is clear and visually organized.

Question 3: Define relational table headings based on your ERD design, emphasizing primary keys (underlined) and foreign keys (marked with *, or in italics). Maintain consistency with your ERD.

Question 4: Use MySQL or SQL Server to create your database, incorporating appropriate CREATE TABLE statements. Highlight any additional constraints applied. Include at least one table with a composite primary key. Define primary and foreign keys within the initial CREATE TABLE statements.

Question 5: Populate all tables with meaningful data, aiming for a minimum of 8 rows per table. Display all data rows using SELECT * from;. Use relevant test data, with a focus on tables at the "many" end of relationships.

SQL & QUERIES:

Questions 6-11: Create SQL queries showcasing the appropriate use of the BETWEEN operator, DISTINCT keyword, TO_CHAR function, aggregate functions, GROUP BY command, and SUBQUERY with OUTER JOIN.

Question 12: Develop a DELETE command to test foreign key constraints and referential integrity. Provide an interpretation of any encountered error messages