Academy of Chinese Culture and Health Sciences SQL Computer Project

User Generated

0009_

Computer Science

Academy of Chinese Culture and Health Sciences

Description

Unformatted Attachment Preview

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? Data Dictionary Management, Data Storage Management, Data Transformation and Presentation, Security Management, Multiuser Access Control, Backup and Recovery Management, Data Integrity Management, Database Access Languages. (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 Major COURSE Course_number, Department SECTION Course_number PREREQUISITE Course_number, Prerequisite_number 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 Course_number Course_dept, Course_number SECTION Course_number Course_dept, Course_number PREREQUISITE Course_number Course_dept, Course_number PREREQUISITE Prerequisite_number Prerequisite_dept, Prerequisite_number 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. 1 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) 2 (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 projectZ. (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. Q5. [ 50 points] Consider the employee database again and try the following complex queries using SQL. You need to answer queries in SQL, and show the results. (a) [ 15 points] For each department, whose average employee salary is more than $28,000, retrieve the department name and the number of female employees working for that department. (b) [ 15 points] Write a SQL to retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company. (c) [ 20 points] Can you make an alternative/better versions these two queries in (a) and (b) ? Use any of query optimization techniques or cost analysis. [ N.B. if you can just make an alternative version and analyze these two versions will be acceptable] Q6. [ 10 points] Consider the employee database again and (a) [ 20 points] Write a SQL query to retrieve the names of all employees in department 5 who work more than 20 hours per week on the ‘ProjectZ’ project. (b) [ 15 points] Write PHP program (code) representation for the part of the database in problem3. You can Create any database table, Insert data, Update/Delete data etc. 3 (c) [ 10 points] Write XML program (core) representation for the part of the database in problem3. You can Create any database table, Insert data, Update/Delete data etc. 4 5 Figure 5.6 One possible database state for the COMPANY relational database schema. 6 7 8 9 Q1. a)Consolidates The Data In A Single Place One of the greatest benefits of an employee database management system is that you will be able to access all essential employee data from a centralized location Improves Data Security It’s highly necessary to protect your employee database from external and internal threats for a variety of reasons, including GDPR compliance, cybersecurity, and your business reputation. Enhances Collaboration Among Employees With an employee directory, employees can quickly find their peers’ contact information at any time. Information including designations, contact numbers, email IDs, seating details, ID numbers, and more will be available. Mitigates Compliance Risks This is where an employee database management system helps the most. As the employee data is stored online, you can access it from anywhere and at any time. Provides Access To The Organization’s Hierarchy With a clear organization tree model, your employees can easily understand the reporting structure and overall hierarchy of your organization. Whenever an issue arises, your employees will know who to contact to resolve the issue immediately. B)In the student table major has to be updated,course table course_number and department has to be updated. Section table course_number has to be updated and in the prerequisite, course_number and prerequisite_number has to be modified. C)COURSE Course_name database Course_number CS13670 Course_dep CS Math SECTION MNT678 Course_number CS123R PREREQUISITE MATH Course_dep CS Course_number Course_dep Prerequisite_no CS1234 CS3380 CS3320 MATH123 CS Math Prerequisite_de p Comp Math Q.2a) Importance of E-R digrams Assist the Database Developer The ER Diagram assists the design even before the construction of tables begins. Requirements Gathering This helps the users to plan how to organize data. Documentation Tool ER Diagrams can work as a documentation to make others understand the core of the database. Logic of the database It communicates the logical structure of the database to the users. Blueprint An ER Diagram is considered as the blueprint of the database. B)E-R diagram for an hospital C)
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hi. Attaching the solutions of questions mentioned by you. Please feel free to ask your doubts here. If you need any editing then let me know. Thank you.

Q1:
(d) Additional users like Resister Office User, Admission Office User and
Transcript Office user can use this database to find information about the
courses, the grades achieved by students, the information related to students,
etc. So, this database can be used by other database users too.
(e) When a column is unique for all rows of column then it is called identifying
column or key of the table. It is also called candidate key of the table. It should
define each row uniquely. For each table,...


Anonymous
Really helpful material, saved me a great deal of time.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags