Database Design and Development

Assignment - Portfolio - TOP Hair dressing

Learning Outcome 1: Use an appropriate design tool to design a relational database system for a substantial problem.

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

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

Scenario

Topic - TOP Hair dressing - Hair dresser, with many clients and treatments carried out.

You have been provided with a topic. You are to supply a report containing the following deliverables: -

Q1. Analyze the business you are devising the database system for. Describe the business rules, and data involved. Explain why the data is important to the information system being developed. Related to your specific topic, discuss the legal and ethical issues involved in managing your data. (P1,M1,D1)

Q2. Using the Oracle Data Modeler Tool DRAW AN ERD DESIGN: AT LEAST 4 OR 5 entities ARE EXPECTED. Include attributes, cardinalities and relationship names. Ensure the diagram is readable. (P1,M1,D1)

Q3. List the Relational Table Headings. Show clearly any primary keys (underlined) and foreign keys (shown with an *, or in italics). Your tables must match your ERD, otherwise 0 marks. Select suitable primary keys for every table. (P1,M1,D1)

Q4. Use MySQL or SQL server and Create your database (ensure you save your CREATE TABLE statement). Highlight any additional constraints added. Ensure AT LEAST ONE table has a COMPOSITE PRIMARY KEY. The primary and foreign keys should be added at the same time as your CREATE TABLE statement, i.e., do not use ALTER TABLE to add them after. (P2,M2,D2)
0 marks if you use any other DBMS or auto-generated DDL code.

Q5. Populate all the tables with data (having 8 or more rows per table). Show all rows, (use SELECT * from <tablename>;) of data in every table created. Good/suitable TEST DATA created. Note, the tables at the many end of a relationship should contain more data. (P3,M3,D3)

SQL & QUERIES:
Devise 6 SQL queries that demonstrate appropriate use of the following:

Q6. Use of the BETWEEN logical operator;

Q7. AN example of DISTINCT within your query

Q8. TO_CHAR function;

Q9. An aggregate function in the SELECT list

Q10. A GROUP BY command

Q11. A SUBQUERY;
At least one of the above queries should include data from more than one table and a different query should demonstrate the use of an OUTER JOIN. (P3,M3,D3)

Include the following for each query:
- A short sentence saying what the query is doing
- SQL Query
- Result

Q12. Devise a DELETE command that tests the foreign keys (testing of the referential integrity of the PK-FK relationship). Explain any error message.

Q13. Use an UPDATE command to test the foreign keys. Explain what the error message means.

Q14. Use an INSERT command to test the primary key (test the entity integrity), explain what the error message means.
(Include the query and comment on any results. Show at least 2 queries for each part (l-n): one that works successfully and one that does not.)