Background Subati Flowers Ltd, is a grower and exporter of over 100 varieties of exceptionally high-quality...

 

 

Transcribed Image Text:

Background Subati Flowers Ltd, is a grower and exporter of over 100 varieties of exceptionally high-quality spray and single head roses grown at an altitude of 2,200m above sea level in their Timau farm. Subati Flowers Ltd export over 60 million stems of THybrid roses a year to 25 different countries around the world. Subati Flowers Ltd keeps information on employees and the departments that they work in. For each department, the department name, internal mail box number, and office phone extension are kept. A department can have many assigned employees, and each employee is assigned to only one department. One employee is usually appointed as a supervisor on annual basis. The company also tracks dependents of each employee. A dependent must be associated with one and only one employee. Some employees will not have dependents, while others will have many dependents. For all dependents, dependent number, first and last names and gender are recorded. All employees are assigned an employee number. This is kept along with the employee's name, hobbies, address, salary, certifications earned, and overtime pay whenever they work outside their working ours. The employees are required to take periodic training to retain their expertise. An employee must take at least three trainings every year. Subati Flowers Ltd tracks all the trainings by date, type, and results (completed vs. not completed). Some employees are assigned a company car. The company cars are identified by their vehicle id, and a license plate number, make, model, and year of manufacture. Subati also has sales rep in the different countries they export the flowers. The sales rep is housed in offices owned by Subati LTD. The details of each sales rep include ID Number, Name, country of resident. The office details include number, location and rent. Subati LTD also keeps information on one next of kin for each of the sales rep. For each next of kin, a unique number, gender first and last names are recorded. Prof. Patrick Kanyi Wamuyu Tasks (a) (b) USIV-Africa Page 1 of 2 Given the information provided on the Subati Ltd background: i. Draw the fully labelled and implementable Crow's Foot ERD for the requirements provided. Include all the entities, relationships, optionalities and multiplicities. ii. Translate the ERD that you have developed into a relational schema. Make sure that you identify all the primary and foreign keys. iii. Create an Access Control (Authorization) Matrix for the tables in your relational Schema. i. ii. iii. Implement your Database design using Oracle SQL*Plus. Use at least one sequence List of your SQL DDL commands (with the necessary integrity and domain constraints). Populate the database. Sample of tuples for each relation (for each relation about 3 records). iy Try five possible queries and Five sub-queries on the database tables Background Subati Flowers Ltd, is a grower and exporter of over 100 varieties of exceptionally high-quality spray and single head roses grown at an altitude of 2,200m above sea level in their Timau farm. Subati Flowers Ltd export over 60 million stems of THybrid roses a year to 25 different countries around the world. Subati Flowers Ltd keeps information on employees and the departments that they work in. For each department, the department name, internal mail box number, and office phone extension are kept. A department can have many assigned employees, and each employee is assigned to only one department. One employee is usually appointed as a supervisor on annual basis. The company also tracks dependents of each employee. A dependent must be associated with one and only one employee. Some employees will not have dependents, while others will have many dependents. For all dependents, dependent number, first and last names and gender are recorded. All employees are assigned an employee number. This is kept along with the employee's name, hobbies, address, salary, certifications earned, and overtime pay whenever they work outside their working ours. The employees are required to take periodic training to retain their expertise. An employee must take at least three trainings every year. Subati Flowers Ltd tracks all the trainings by date, type, and results (completed vs. not completed). Some employees are assigned a company car. The company cars are identified by their vehicle id, and a license plate number, make, model, and year of manufacture. Subati also has sales rep in the different countries they export the flowers. The sales rep is housed in offices owned by Subati LTD. The details of each sales rep include ID Number, Name, country of resident. The office details include number, location and rent. Subati LTD also keeps information on one next of kin for each of the sales rep. For each next of kin, a unique number, gender first and last names are recorded. Prof. Patrick Kanyi Wamuyu Tasks (a) (b) USIV-Africa Page 1 of 2 Given the information provided on the Subati Ltd background: i. Draw the fully labelled and implementable Crow's Foot ERD for the requirements provided. Include all the entities, relationships, optionalities and multiplicities. ii. Translate the ERD that you have developed into a relational schema. Make sure that you identify all the primary and foreign keys. iii. Create an Access Control (Authorization) Matrix for the tables in your relational Schema. i. ii. iii. Implement your Database design using Oracle SQL*Plus. Use at least one sequence List of your SQL DDL commands (with the necessary integrity and domain constraints). Populate the database. Sample of tuples for each relation (for each relation about 3 records). iy Try five possible queries and Five sub-queries on the database tables