Database Design and Implementation: A Practical Guide with Examples, Assignments of Relational Database Management Systems (RDBMS)

Download Database Design and Implementation: A Practical Guide with Examples and more Assignments Relational Database Management Systems (RDBMS) in PDF only on Docsity!

Figure 1 : Creating and using database employee Figure 2 : Creating table DEPT

Figure 3 : Creating table EMP with check constraint Figure 4 : Populating DEPT table

Figure 7 : Employees who earn more than their manager

Figure 8 : Department where all employees earn more than 4000

Figure 9 : Department with largest number of employees

Figure 10 : Creating view MANAGERS with all employee details

These relationships between the entities are shown along with their attributes, primary key and foreign key in the ER Diagram above.

3(b)

Relation Schemas generated for the above Entity Relationship Diagram are as follows: Branch: Column name Data type Length Primary key Foreign key Unique Nullable branch_no integer 10 Yes No Yes No unit_number varchar 10 No No No Yes street_number varchar 10 No No No No street_name varchar 50 No No No No suburb varchar 50 No No No No state varchar 50 No No No No postcode integer 10 No No No No branch_telephone varchar 20 No No Yes No branch_fax varchar 20 No No Yes No Manager: Column name Data type Length Primary key Foreign key Unique Nullable branch_no integer 10 No Yes No No Employee_no integer 10 No Yes No No Employee: Column name Data type Length Primary key Foreign key Unique Nullable employee_no integer 10 Yes No Yes No employee_name varchar 100 No No No Yes employee_address varchar 100 No No No No employee_telephone varchar 20 No No Yes No annual_salary integer 10 No No No No join_date date - No No No No supervisor_id integer 10 No Yes No Yes branch_no integer 10 No Yes No No Employee_Hobby: Column name Data type Length Primary key Foreign key Unique Nullable employee_no integer 10 No Yes No No

hobby_no integer 10 No Yes No No

max_weekly_rent integer 5 No No No No