WaRIA-Ki Ltd Database Miniworld Waria-Ki, a Confectionery Manufacturing company has several departments. A department may have many employees but must have at least seven employees. Every employee...

WaRIA-Ki Ltd Database Miniworld

Waria-Ki, a Confectionery Manufacturing company has several departments. A department may have many employees but must have at least seven employees. Every employee works for one and only one department. Every department has a manager. Employee’s details include, employee number, first name, last name, middle initial, gender, address, hobbies and salary. Department details include department number, department name, location and a phone number. The department number and department name are both unique identifiers of a department. Waria-Ki 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. Each employee is assigned an office. Some employees do share offices, but an employee must be allocated an office. Office details include Office number, location and office phone number. Some employees are assigned a company car. The company cars are identified by their vehicle id, and have a license plate number, make, model, and year of manufacture. Waria-Ki manufactures many sugar confectionery products including gum and jelly products, hard sweets, lollipops and Chewables. Each of these products are produced by a particular department. Product details include product number, description, manufacture date and the current price. The products are usually distributed for storage in one or many of the company’s warehouses. A warehouse may store one or many type of products. A warehouse details include code, location and phone number. Waria-Ki products are very popular among many kiosk owners. Each kiosk owners can buy any number of products, and each product can be bought by any number of kiosk owners. Kiosk owner’s details include code, last name, first name, kiosk location, address and email.

Tasks

Prof. Patrick Wamuyu Page 1 of 2

USiU-Africa

1. 
   

   (a) Database Design:Given the information provided on the Waria-Ki:
   i. Draw a 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.

   
   


2. 
   

   (b) Database Implementation:Implement your Database Design using Oracle APEX.

   
   
   
   
   1. 
      

      Use at least three sequences

      
      
   
   
   2. 
      

      Include all the necessary integrity and domain constraints.

      
      
   
   
   3. 
      

      Populate the database. Sample of tuples for each relation (for each relation about 5 records).

      
      
   
   
   
   


3. 
   

   (c) Database Implementation:Include advanced features: -

   
   
   
   
   1. 
      

      2 Views (involving two different relations and with different limits to

      
      

      modifications)

      
      
   
   
   2. 
      

      2 different procedures

      
      
   
   
   3. 
      

      2 different synonyms

      
      
   
   
   4. 
      

      4 different triggers

      
      
   
   
   5. 
      

      Index two tables

      
      
   
   
   
   


4. 
   

   (d) Web Application Development

   
   

   i. Create a fully functional APEX Web Application for the Waria-Ki

   
   

   ii. Allow the user [email protected] and two other users to access your application.

   
   


5. 
   

   (e) Make a presentation of the project in Class on Wednesday, December 1, 2021, 7:20pm.

   
   


6. 
   

   (f) Submit the following on Wednesday, December 1, 2021, 3:00pm.

   
   

1. 
   

   ERD

   
   


2. 
   

   Relational Schema

   
   


3. 
   

   Sequences Scripts

   
   


4. 
   

   Triggers Scripts

   
   


5. 
   

   A Screenshot of your Application Homepage

   
   


6. 
   

   The [email protected] details