Database Assignment:
1. When defining a domain what are you trying to describe?
2. Give an example of a domain for a social security number?
3. A Doctor (the user of the database) has asked for an additional field to be added to the patient table. There are many reports that will use this field, but there are others that will not. Explain what should happen to this database in that other doctors and administrators may use it as well
4. Do you agree with the concept that a good database will have no repeated data in the tables? Back up your answer
5. (See surgery table) A patient may have more than one diagnosis associated with the surgery (represented by the dcode) and more than one procedure code associated with the surgery(represented by pcode) For example, the patient could have two procedure codes and three diagnostic codes for each surgery. What is wrong with the data in the table to the right?
|
Surgery Table
|
|
Soc
|
Surgery_date
|
pcode
|
dcode
|
|
1
|
1/7/2000
|
571
|
72
|
|
2
|
1/7/2000
|
1094
|
72
|
|
3
|
1/7/2000
|
1089
|
180
|
|
4
|
1/7/2000
|
74
|
1
|
|
5
|
1/5/2000
|
69
|
193
|
|
6
|
1/7/2000
|
3
|
1
|
|
1
|
1/1/2000
|
0
|
0
|
|
8
|
1/5/2000
|
33
|
21
|
|
9
|
1/5/2000
|
32
|
20
|
|
2
|
1/5/2000
|
33
|
20
|
|
11
|
1/7/2000
|
0
|
85
|
|
12
|
1/7/2000
|
0
|
85
|
|
13
|
1/7/2000
|
3
|
1
|
|
14
|
1/7/2000
|
112
|
1
|
|
15
|
1/7/2000
|
0
|
1
|
6. Using the ER design below (keys defined with *), how may tables will be defined and what data will be in the tables?
7. In the Access Relationship screen below, what can you say about the demograph table? What can you say about the social security number in the demograph table and all of the other tables?
8. Using the handout for the Sales database, could cust_id and inv_date be a key for the invoice table? Why or why not?
9. Using the handout for the Sales database, explain referential integrity?
10. Using a hospital database, give an example of data inconsistency that could exist in a database that was poorly designed
11. Using a hospital database as an example, list three different external views of the data in the database
12. What is data independence?
13. Describe what a key is and give an example of a possible key if you were creating a patient appointment table
14. Using the book-order database relationship screen(included with this exam), list 5 constraints you could define
15. Using the Access relationship handout for the Book Order Database, what can you say about the prof_code in both the Professor and Course_offerings tables?
16. Select two tables from the mail order database and prove if they are in 2nd and/or 3rd normal form.
17. Your Uncle has a landscape business. He has a lot of repeat customers, so it is very important to keep track of what services they have done as well as the satisfaction of the work. Lately there have been a few complaints, therefore you will need to keep track of which employees are involved in each job for the customer. Your uncle also wants to keep track of all materials used on each job. Draw the ER diagram.
SQL section:
1. List all publishers who do not have an email
2. Add another record to the courses table
3. Add 1 to year field in the book_order table
4. List all Professors who have no course offerings
5. Write the statement to define the setup_info table
6. Count the number of book orders for each year and semester
7. Add the foreign key between the tables publisher and book_detail
8. List all course offerings grouped by semester and year
9. List the professor name, the course name and number and the ISBN for all books ordered for the course
10. Make up an SQL statement of your own that involved at least two tables. List what your query does.