FOR THE MIDTERM:
	You MAY make a review sheet
	may NOT run access or internet search during the exam
	Canvas only if allowed as part of the question
	if unsure, ASK
	report sources
fair questions
	watch for repeating groups
	read an FD diagram
	1NF, 2NF, 3NF, WHY
	ask you to put in 2NF but not 3NF
	design queries (QBE SQL)
	show result of query
	clinic database
I will not ask questions about:
	4NF
	PJNF

normalization
	inconsistency is NOT by row
	cannot know WHICH value is correct
	A=>B is a one way relationship
		A1=>B1 and A2=>B1 is OK
		(zip code determines country)
		A2=>B1 and A2=>B2 is NOT OK
	multi-attribute key is OK
		(discipline, courseNum)
	watch for repeating groups/non-atomic
		(Tookey, Skoda)
	watch for depending on PART of the primary key
	watch for transitive dependancies
	"The KEY, the WHOLE KEY, and NOTHING BUT THE KEY"

	
	
CSC 115 Database Midterm Review
The format will be to answer 6 of 8.

This is a database keeping track of local religious congregations.
clergyTable
ID, clergyName, clergyAddress, clergyPhone, clergyEmail
congregationTable
ID, congregationName, worshipTimes, address
denominationTable
ID, denominationName, type, address, phone
PART
congregationID, denominationID
preachTable
clergyID, congregationID

IF you do not understand this set of tables, ASK.
Question:
A. Which of the tables are for entities? How do you know?
B. Which of the tables are for relationships? How do you know?
C. Identify the primary key for each table.

This is a database keeping track of local religious congregations.
clergyTable
ID, clergyName, clergyAddress, clergyPhone, clergyEmail
congregationTable
ID, congregationName, worshipTimes, address
denominationTable
ID, denominationName, type, address, phone
PART
congregationID, denominationID
preachTable
clergyID, congregationID

IF you do not understand this set of tables, ASK.
Question:
Explain the purpose of this query, and what kind of information you get from it.
SELECT clergyTable.clergyName, congregationTable.congregationName
FROM (clergyTable INNER JOIN preachTable ON clergyTable.ID = preachTable.clergyID) INNER JOIN congregationTable ON preachTable.congregationID = congregationTable.ID;

This is a database keeping track of local religious congregations.
clergyTable
ID, clergyName, clergyAddress, clergyPhone, clergyEmail
congregationTable
ID, congregationName, worshipTimes, address
denominationTable
ID, denominationName, type, address, phone
PART
congregationID, denominationID
preachTable
clergyID, congregationID

IF you do not understand this set of tables, ASK.
5. Write a query to list congregations, in order by denomination.

6. In at most one paragraph each, explain TWO of the following concepts.  
a. What is redundancy and why is it important in Databases?
b. What is an key, and why is it important in Databases?
c. What is the difference between QBE and SQL?

Question:
The English department is compiling a book of the complete works of poetry of a former faculty member, known for her poetry. For each poem, you should keep track of:
The usual information about a poem (title, author…)
Publications (minimal bibliographic information)
Topics/themes addressed by the poem
Section of the book the poem will be included in.

Create a FD diagram to represent what is determined by what with regard to your poems database.

QUESTION:
 Given the FD diagram above, create draft table or tables.
If you use more than one table, explain why.

Given your draft table or tables and the FD diagram above, check your draft for normalized form.
Be specific about why a table is or is not normal, and why you did what you did.

3. Given the FD diagram above and data below, look for an inconsistency.
Explain what the inconsistency is, or why there isn’t one.

I	B	A	P	S	G	C
i1	b1	a1	p1	s1	g1	c1
i2	b2	a2	p2	s1	g1	c1
i3	b3	a3	p1	s2	g2	c2
i4	b4	a4	p2	s2	g2	c2
i5	b5	a5	p5	s1	g2	c2
i6	b6	a6	p7	s2	g2	c2

Instructors teach Sections, (e.g. CSC115A).  
Sections have meeting times, (e.g. M9,W9,F9.)  
A faculty committee want to meet at a time during the day when none of them are in a class.

Question:
 Given this draft ER, and the situation, both above, create draft tables.
If you use more than one table, explain why.

Given your draft tables, and situation, check for normalized form.
Be specific about why a table is or is not normal, and why you did what you did.

Question:
A student decided to save time by putting all the data into one table.
His roommate, also a Database student, suggested he split the tables as indicated.

Original table

Split tables

ANSWER EITHER
What is gained by splitting the tables, if anything?
OR
How would the data look in the split tables?
You do not need to answer both.

7. Given tables below , draft a query to list sections, including teacher name, for StudentName=’Fred’.

Tables:

You should be comfortable with questions on these topics:
Given situation, create FD
Given FD, create draft tables.
Given draft tables and FD, check normalized form.
Given FD and data, look for inconsistency.
Given situation, create draft ER.
Given draft ER, and situation, create draft tables.
Given draft tables, and situation, check normalized form.
Given data in big table, discuss split tables.
Given tables, draft a query.
Interpret SQL query
Write SQL query