Indexes and keys etc
Problem #1: Set up a patient table with a patient id, name, address, code for their primary care doctor.
Set up a doctor table that has the code for the doctor, their name, type of doctor.
The patient table should have a primary key and the code should be a foreign into the doctor table.
The patients name should be set up as an index.
Problem #2: Design and develop a set of tables for a payroll system using the information below.
Set up the necessary tables for this database and populate them with data. You need to create the
appropriate primary keys and foreign keys. Your must design a relational database following the rules
to achieve third normal form. If you want me to look at your design before you implement, please send it
to me with a comment in the subject that tells me what it is.
You need to use a check constraints, a unique constraint and a null constraint somewhere in this
development. You should also create an index.
We will look at a very simple version of payroll. Your payroll has to carry the following information:
- Employee Id
- Employee Name
- Salary
- Tax percent withheld
- Medical withheld
- Other deductions withheld
- Projects the person is worked on (note there can be more than one project)
- Name of the project (each project should have a name)
- Manager of the project (each project is assigned a manager)
- Hours the employee worked on the project (each time they worked on it)