Exercise 1 (Knowledge Questions) [22 points]

Please provide concise but precise answers.

• [4 points] Explain the following terms briefly and describe their relationship.
  • Database o DBMS
• [3 points] Briefly explain what a data model is and which kind of data models a DBS can have.
• [4 points] What is the ER model? Explain its three main components.
• [3 points] Describe the data abstraction levels that DBS can have.
• [4 points] What are the logical data independence and the physical data independence?
• [4 points] Explain the following terms (use the notions learnt in the lecture) and provide an example for each term. o [1 point] Generalization o [1 point] Aggregation
  • [2 points] What is a ‘table’, the purpose of it, and what language do we use to create it in database systems?

Exercise 2 (Oracle) [40 points]

Consider the following table ‘Employees’.

Use your CISE Oracle account to create this table and perform the operations below. Provide SQL statements for all operations. Show your SQL queries and the outputs of all results as screen snapshots in Oracle.

• [6 points] Create the EMPLOYEES table and insert all records into the table.

• [6 points] Find the names of employees who were hired before 2016.

• [5 points] Find the number of employees who live in Gainesville and work as a salesman.

• [6 points] Find the names and salaries of all employees whose salary is between $1200 and $1500 and who work in Gainesville.

• [6 points] Find the names of employees who are female and worked as manager.

• [5 points] Display the names of all employees along with their salaries in descending salary order.

(Hint: Find out in the Oracle manuals how to sort data in descending order.)

• [6 points] Find the names of employees whose name has more than two ‘a’ in it and ends with ‘s’.

(Hint: Look up the command like in the Oracle manuals and apply it.)

Exercise 3 (ER Model) [19 points]

Design an Entity-Relationship diagram that models a hospital management system and considers the requirements listed below. That means that you have to identify suitable entity sets, relationship sets, attributes, keys of entity sets(if not specified), and so on. Further add the cardinalities (1:1, 1:m, m:1, m:n) to the relationship sets and write down your assumptions regarding the cardinalities if there could be a doubt.

Consider the following requirements about a hospital management system:

• An employee has a unique ID, an email address, a name, a date of birth, and an age. Age is a derived attribute.
• There are two types of employees: Doctors and Nurses.
• Employees belong to a department that has a department ID, a name, its chair’s name, and an office address. The address includes street, city, state, and zip code.
• Doctors have a specialty and an office number. They can send messages to other doctors.
• A message includes a title and contents.
• A patient information includes a name, an email address, a date of birth, an age, a phone number, and a gender. Age is a derived attribute.
• A doctor diagnoses a patient, and a prescription is written whenever a diagnosis is made.
• Nurses govern rooms that have room IDs, availability, and room type, and patients are assigned to a room.
• When a patient is assigned to a room, start and end date to the room will be recorded.
• Patients pay for prescribed medicine, and a medicine has a price, a quantity, and a code.

Exercise 4 (ER Model) [19 points]

Design an Entity-Relationship diagram that models an online course management system and considers the requirements listed below. That means that you have to identify suitable entity sets, relationship sets, attributes, keys of entity sets(if not specified), and so on. Further add the cardinalities (1:1, 1:m, m:1, m:n) to the relationship sets and write down your assumptions regarding the cardinalities if there could be a doubt.

Consider the following requirements about an online course management system:

• Every course has a unique ID, a title, and start and end dates.
• Each course might have prerequisites.
• Each course is taught by one instructor.
• Each instructor has a name, gender, email address, profile picture, and affiliation.
• Instructors can belong to an agency company that has a name, a reputation, and a number of instructors as attributes.
• Users can take multiple courses.
• Each user has a unique ID, a name, an email address, a date of birth, and a gender.
• A user can write reviews for courses. A review has an ID, a content, a score, and a timestamp.
• Each course offers multiple assignments. Assignments have an ID, a deadline, and a content.
• Users answer the assignments that the course offers.