a)  Figure 1 shows an Entity Relationship diagram (Conceptual model) for a university dining     service. This organisation provides dining services to a major university. Note: the attribute Skill in the Staff relation is a multi-valued attribute.

[10 marks]

Please transform the ER model (in Figure 2) to a relational schema (i.e. logical design):

(i)  Diagram all the functional dependencies

(ii) Show all relations including any new relations that need to be created as part of the logical design process.

(iii)  Clearly indicate ALL primary and foreign keys for each relation.

Question 3 Create an ER Model

Projects, Inc., is an engineering firm with approximately 500 employees. A database is required to keep track of all employees, their skills, projects assigned, and departments worked in. Every employee has a unique number assigned by the firm, required to store his or her name and date of birth. If an employee is currently married to another employee of Project, Inc., the date of marriage and who is married to      whom must be stored. Each employee is given a job title (for example, engineer, secretary, and so on).  An employee does only one type ofjob at any given time, and we only need to retain information for an employee’s current job.

The firm has a number of different departments, each with unique name. An employee can report to only one department.    Each department has a phone number.

To produce various kinds of equipment, each department deals with many vendors. A vendor typically  supplies equipment to many departments. We are required to store the name and address of each vendor and the date of the last meeting between a department and a vendor.

Many employees can work on a project. An employee can work on many projects. (for example,            Southwest Refinery, Cambridge Petrochemicals, and so on) but can only be assigned to at most one       project in a given period of time. An employee can have many skills (preparing material requisition,      checking drawings, and so on), but she or he may use only a given set of skills on a particular project.    (For example, an employee MURPHY may prepare requisition for Southwest Refinery project and        prepare requisition as well as check drawings for Cambridge Petrochemicals.) Employee uses each skill that they possess in at least one project. Each skill is assigned a number, and we must store a short         description of each skill. Projects are distinguished by project numbers, and we must store the estimated cost of each project.

a)        Based on the provided information, draw an Entity-Relationship (ER) Diagram using the Crow’s foot model symbols and include all attributes.

(a) Identify all possible entities and relationships.

(b) Identify the main attributes in each entity including all primary and foreign keys (c) Identify the mandatory/optional dependencies for all the relationships.

(d) Resolve all M:N relationships.

Question 4

The data given in the table below is about  students during a  specific year. The table is  susceptible to data redundancies which might lead to data anomalies. Carefully analyse the table and answer the following questions:

i)    Which normal form does the structure of the table conform to? Briefly explain your answer. (2 marks)

ii)    Provide examples of how insertion, deletion, and modification anomalies could occur in this table. (3 marks)

iii)   Identify all the dependencies using a dependency diagram or use arrows to clearly represent all the dependencies. (6 marks)

iv)   Using the dependencies identified in (iii) above, convert the table to 2NF and 3NF. Draw the ERD of the 3NF. (3+3+3 marks)

Question 5

The Excel Property Services database has been set up to manage clients’ enquiries for properties the company lists on behalf of their property owners. A brief description of the database, an ER diagram, the structure and sample data of each table are shown in Appendix-A.

Note: For queries that require JOINS, you must use the ORACLE JOIN syntax that you learnt in this course. You may lose marks if you use JOIN syntax that will not work in Oracle.

Read Appendix-A carefully and write SQL statements for each of the scenarios given below:

i.   The staff table stores the annual salary for each staff member.    Generate a report to display the staff number, full name (the combination of first and last names), and monthly salaries for all    staff members. The column headings should be replaced by Staff ID, Staff Name, and Monthly Salary respectively. [4]

List the client number and viewing date of all viewings on property PG4 where a comment has not been supplied. [4]

iii.   List details (Property Number, Type, Rooms, and Rent) of all properties arranged in ascending order of property type, and in descending order of rent within each property type. [4]

iv. List the details (staff number, staff first name, staff last name, and position) of staff who work in the same branch as the branch located at ‘135 Albert St’[4]

v.  For each branch, list the staff numbers and names of staff (first and last names) who manage      properties, including the city in which the branch is located, the branch number, and the             properties (property number) that the staff manage. Sort the results in ascending of staff number within each branch number    [9]

List details (branch number, city) of all branch offices and any properties (property number, street, and city) that are in the same city. Your output should include all branches including those that that do not currently have any properties. [5]