Design a database schema with the following specifications
• Employee (SSN, Fname, LName, Sex, Address,
DOB, Salary) • Department (DeptNum, Location, DeptName) • Project (PrjName, Number, Location) • Dependents (DName, Sex, DOB, Relationship) Identify any extra attribute that you have to declare in the tables to create the following associations: • Employee-Project • Employee-Department • Employee-Dependent • Department-Project
Insert at least 4 records in each table and show the following: a) What happens if you try to associate an employee with the department table if the employee does not exist?
b) What happens if you try to delete an employee record if it is associated with several other tables such as Department or Dependents?
c) Run a command to order records in the Department table in descending order based on attribute DeptName.
d) Identify the weak entity in this database schema. Mention what changes you made in the table to associate it with any other entities.
e) Run separate queries to show results of full outer join and inner join on Employee and Dependents table. (Hint: to see expected results ensure that some employees don't have dependents
Recently Asked Questions
- trying to find the right code for INSTEAD OF INSERT trigger lets say the trigger is called insert_shippers on the Shippers table and the trigger prevents
- Topic 2: Basic States of Data What are the three basic states of data? With regards to security, where should we spend the most effort?
- Security should not be an afterthought. The security model should be identified early in the design phase of a software project. Application Security Models