BCO2149 Database Systems Assignment 2, S2 2016 Total Marks 40 Due: Instructions: Students are required to attempt each question. Question 1 For each section in this question students are required to develop an Entity Relationship diagram from the following information. Relationships identified in your diagrams must show both minimum and maximum cardinality. (a) Super Construction is an engineering company that works on several construction projects at any one time. Each project may require a number of employees to work on it and an employee must work on at least one project. The date an employee starts and ends works on a project must be recorded and the number of hours they worked on the project that day must also be recorded. An employee may work on the same project several times and this would be distinguished by the different start and end dates. An employee must have at least one qualification and that qualification may be held by many other employees. For each qualification held by an employee, the year and the institution it was received from must be recorded. An employee has at least one title and a title may be held by many employees. The construction company would like to record all the titles an employee has had within the company. The date to and date from should be recorded for each title an employee has had. A project may require a number of tasks to be completed on it and that same task may be required to be completed on many other projects. The duration time for a particular task on a specific project needs to be recorded. The duration of a task may vary depending on the project. A project is assigned one employee as its overall manager. An employee may manage many projects but a project is managed by one and only one employee. Each task may require a number of parts to be used in order to complete it. A part may be used on many tasks or none at all. The number of parts used on a particular task may differ depending on the nature of the project. The number of parts used per task and project needs to be recorded. Each employee must belong to one department and a department must have at least one employee assigned to it. An employee may supervise one or more other employees and each employee is supervised by only one supervisor. (Employee) An engineer must have at least one qualification and that qualification may be held by many other engineers. For each qualification held by an engineer, the year and the institution it was received from must be recorded.(b) (i) A doctor consults from one clinic only, but a clinic may have many doctors working from it. A patient makes an appointment to see a doctor on a specific day and time. A patient may consult with many doctors and a doctor must consult with at least one patient. (ii) What changes, if any, would need to be made to model developed above in (i), if the following to the scenario was included. A doctor now works from many clinics and a clinic can have many doctors operating from it. When a patient makes a booking to see a doctor the time, day and clinic must be recorded. (8 + 4= 12 marks) Question2: Students are required to develop an Entity Relationship diagram from the following scenario. (8 marks) Temporary Employment Corporation (TEC) places temporary workers in Compnaies. TEC's manager has provided the following information about the how the business operates. TEC has a file of candidates who are willing to work. Any candidate who has worked before has a specific job history. (Naturally, no jog history exists if the candidate has never worked for the company before). Each time the candidate accepts a work placement; one additional job history record is created. Each candidate may have earned several qualifications. Each qualification may be earned by more than candidate.(For example, more than one candidate may have earned a Bachelor of Business Administration degree or a Microsoft Network Certification and clearly a candidate may have earned both a BBA and a Microsoft Network Certification. TEC offers courses to help candidates improve their qualifications. Every course develops one specific qualification; however, TEC does not offer a course for every qualification. Some qualifications are developed through multiple courses. Some courses cover advanced topics that require specific qualifications as prerequisites. Some courses cover basic topics that do not require any prerequisite qualifications. A course can have several prerequisites. A qualification can be a prerequisite for more than one course. Courses are taught during training sessions. A training session is the presentation of a single course. Over time, TEC will offer many training sessions for each course. However, new courses may not have any training sessions schedules right away. Candidates can pay a fee to attend a training session. A training session can accommodate several candidates, although new training sessions will not have any candidates registered at first. TEC has a list of companies that request temporaries. Each time a company requests temporary employee, TEC makes an entry in the Openings folder. That folder contains an opening number, a company name, required qualifications, starting date, anticipated ending date, and hourly pay. Each opening requires only one specific or main qualification.When a candidate matches the qualification, the job is assigned, and an entry is made in the placement record folder. The folder contains such information as an opening number, candidate number, and total hours worked. In addition, an entry is made in the job history for the candidate. An opening can be filled by many candidates, and a candidate can fill many openings. TEC uses special codes to describe a candidate's qualifications for an opening. The list of codes is shown in the table below; Code Description SEC-45 Secretarial work; candidate must type at least 45 works per minute SEC-60 Secretarial work; candidate must type at least 60 works per minute CLERK General Clerking duties PRG-VB Programmer, Visual Basic PRG-C++ Programmer, C++ DBA-ORA Database Administrator, Oracle DBA-DB2 Database Administrator, IBM DB2 DBA-SQLSERV Database Administrator, MS SQL Server SYS-1 System Analyst, Level 1 SYS-2 System Analyst, Level 2 NW-NOV Network Administrator WD-CF Web Developer Using the following as entities, Students are required to develop an ER model that could satisfy the requirements for TEC's management Entities: Company, Opening, Qualification, Candidate, Job History, Placement, Course and SessionQuestion 3: (a) Students are required to develop a set of relational database tables from the following Entity Relationship diagram. Primary and foreign keys must be highlighted.(b) Students are required to develop an Entity Relationship diagram from the following set of relational tables. Only the maximum cardinality of each relationship needs to be displayed. The Orders database maintains information on Customers, Sales Representatives, Departments, and Orders. The tblCustomer table maintains information relating to Customers. The tblDepartment table stores information relating to full name of each department. The tblSales_Rep table stores information on all Sales Representatives and the Department they belong to. The field supervisorNO represents a sales rep number who is the current supervisor of a particular sales rep. The tblOrder table stores information relating to Orders, a unique Order number, the date the Order was placed and the Customer who placed the order. The tblOrderLine table maintains information relating to Products that were ordered on each specific Order. Purchase Price represents the Product's Unit Price at the point of sale. Products can be stored in many warehouses. The amount of each product stored in a particular warehouse must be recorded. Each is sourced from one supplier and a supplier can supply many products. Orders Database tblCustomer( Cust_NO, F_Name, L_Name, Street, Suburb, Postcode, Balance) tblDepartment( Depart_NO, Depart_Name) tblSales_Rep( Sales_RepNO, Surname, F_Name, Depart_NOfk, SupervisorNOfk) tblOrder( Order_NO, Cust_NOfk, Ord_Date, Sales_RepNOfk, Ord_Status) tblOrderLine( Order_NO, Product_NO, Qty_Purchased, Purchase_Price) tblProduct( Product_NO, Prod_Description, Qty_On_Hand, Unit_Price, CategoryNOfk, SupplierNOfk) tblCategory( CategoryNO, CategoryName) tblWareHouse(WarhouseNO, WareHouseName) tblProductStorage(WarhouseNO, ProductNO, Quantity_on_Hand) tblSupplier(SupplierNO, SupplierName) (10 + 10= 20 marks)