Coursework in Data Modeling, Database Design, and SQL Querying

Learning Outcomes

1. Produce a data model that accurately reflects a business scenario.

2. Produce a database design based on a given data model.

3. Implement the design using a relational database management system such as Apex, and Oracle, and produce SQL queries to meet given user requirements.

Draw an entity-relationship diagram that represents the information requirements above for CHI-22, New Orleans. In particular, you are required to establish the association between the entities: seminars, attendees, auditoriums and dates.  

• Using your ER diagram in part a, give a brief descriptionand an example of each of the following.   
• Key    
• Composite Key
• Key Constraint
• Total Participation

CHI-22 seminar event’s principal organizers: Olivia, Eugene, Pope, Leign-Ann, and Egginton, uncovered new fundamental information requirementsat their last pre-event planning meeting. Nonetheless, they also concluded that some of these new requirements may not be easily captured in an ER diagram. The additional business rules are:

• Each attendeehad to register to attend at least one seminar.
• Every seminarconfirmed had to have an associated principal organizer among the attendees.
• Attendeesmay not be able to register for two separate seminars on the same business day.

 Which two requirements above can be represented in an ER diagram?

Show the required modifications to your ER diagram for oneof those two new requirements.

The related information requirements for Northumbria University on-site car parking facilities for staff have been captured in Figure 1.2.  Every car park will have a fixed number of parking spaces and every car parking space will have a unique ID. Additionally, data on the capacity for each car park held in a separate record has been previously shown to you — the System Analyst. Members of staff can apply to use a single parking space exclusively; the following personally identifiable information would be required: staff number, name, work telephone number and vehicle registration number.

• Draw an entity-relationship diagram that represents all of the aboveinformation requirements for Northumbria University Staff Parking.
• State any business rules and assumptions you have reached necessary to support your ER Model.

The Oxleas Mental Health Hospital, London offer a wide range of health and social care services to people living in south east London and parts of Kent. Its community health care unit offer services such as district nursing and health visiting, care for people with learning disabilities and mental health care such as psychiatry, nursing and therapies. Further, its multidisciplinary teams look after people of all ages and we work in partnership with other parts of the NHS, local councils and the voluntary sector.

The Outpatient Prescription Sheet for Oxleas Mental Health Hospital is shown below in Figure 1.3.

The data entry form shown in  is susceptible to various update anomalies. Give examples of the insertion, deletion and update anomalies in the data.

 

You are to examine the Outpatient Prescription Sheet,then identify the functional dependencies amongst the attributes. Also, you are required to articulate any assumptions you’ve reached about the data supplied and the attributes shown in this form.

 

Detail and demonstrate the process of normalizing the attributes shown in the form to produce a set of well-designed 3NF relations.  Identify the primary, alternate, and foreign keys in your 3NF relations.

Section C — Logical Database Design and Oracle SQL Implementation/Querying

 You are tasked to implement and subsequently query an ORACLE database using the APEX server. The database is derived from the given tables set in Appendix B.  You must work individually for this assignment.  

You need to create all the tables listed in Appendix B. Make sure the appropriate fields are defined as key, and that other suitable data integrity rules are enforced. All table names should be prefixed your student ID.  For example, if you create a table called customer, and your student id is p123456 then you should name the table p123456_customer. 

All attributes within each table should be prefixed with your initials.  For example, it you create an attribute called firstname within the customer table and your name is Charlie Enfield Brown then your column name should be ceb_firstname.  

Populate your Oracle tables with the given data and then add 5 more rows in each table with more data.

Using the tables provided in Appendix B, define and run SIX new queries of your choice. Each query should require TWO or more of the following querying facilities, (and all of these facilities should be used at least once in your set of queries) and should be properly justified as to why the query would be useful to the SecondHome case study organisation:

• Selection of particular table columns
• Join of at least 2 tables
• Use of count and/or another similar mathematical expression
• Use of a sorting/ordering facility
• A condition using “<”, “>”, LIKE etc.
• A condition using IN, NOT NULL, or similar.
• A sub-query

In addition to the above, you are required to choose SIX queries from Appendix A, implement them in SQL, run the queries and document the results. You are required to document reasons why you chose each query and also the results of the query with the corresponding SQL.