05 Mar Write at least FIVE advantages of using database systems for any industry employees?

Q1. [ 50 points] Given an example database that stores students and course information (Fig. 1.2 from the book).

(a) [ 10 points] Write at least FIVE advantages of using database systems for any industry employees? 

(b) [ 10 points] If the name of the ‘CS’ (Computer Science) Department changes to ‘CIS’ (Computer Information Systems) Department and the corresponding prefix for the course number also changes, identify the columns in the database that would need to be updated, and fill-up the table below:

Table Column(s)

STUDENT

COURSE

SECTION

PREREQUISITE

(c) [ 10 points] Can you restructure/split the columns in COURSE, SECTION, and PREREQUISITE tables so that only one column will need to be updated?

Table Column Split Columns

COURSE

SECTION

PREREQUISITE

(d) [ 10 points] Can we consider additional users (example Resister Office User, Admission Office user Transcript office users), can use this database?   Explain your answer.

(e) [ 10 points] In the fig 1.2, Identify the column or the group of columns that must be unique across all rows in the table? Explain your answer. 

Q2. [ 50 points]  E-R diagram

(a) [ 10 points] Why do we need an E-R diagram in a database systems management?

(b) [ 25 points] Design a detailed E-R diagram for a Hospital Emergency Management Systems. 

(c) [ 15 points] Which of the following EER diagram(s) is/are incorrect and why? State clearly any assumptions you make. 

Q3. [ 50 points] Consider the following relations for a database that keeps track of visits of various doctors working for a group of hospitals, to various hospitals under the group:

DOCTORS(DSsn, Name, Start_year, Dept_no, location) 

VISIT(DSsn, From_hospital, To_hospital, Departure_date, Return_date, Visit_id) 

EXPENSE(Visit_id, Account#, Amount)  

(a) [ 25 points] How can you create the table using SQL? Also, show us how you can insert five data values (at least three with the location name New York). 

(b) [ 15 points] Identify different database keys for this schema, draw the schema diagram, stating any assumptions you make. 

(c) [ 10 points] Write the SQL to list the doctors in New York in descending order by name. 

Q4.  [ 50 points] Consider the COMPANY relational database schema. Write SQLs and show the result of each query if it is applied to the COMPANY database (attached). 

(a) [ 10 points] Retrieve the names of all male employees in department 5 who earns more than 3000 and works on ProductZ project. 

(b) [ 10 points] List the names of all employees who are from Houston, Texas and works under the manager James Borg.

(c) [ 10 points] Find the names and location of all employees who are working in the project ProductZ. 

(d) [ 10 points] To give COVID-19 benefits list name of all dependents of Mr. John Smith. 

(e) [ 10 points] If any employee works for 40 hours or more, we can consider him/her as a “full-time” employee. Similarly, if an employee works for 20 hours or more, let us consider him/her as a “half-time” employee. Write a SQL query to find all “half-time” employees from the employee database.