Dental Clinic Database
Show a count of all expensive procedures (>1500) covered by Manulife of Blue Cross
performed by all DDS certified
dentists. Breakdown the results to show a count and total value for each dentist
patient pairing.
Result should show dentist info and patient info.
- Which tables are needed? patients, dentists, procedures, pro_dens, insurance,
ins_pros, appointments, app_pros
|| = concatination
SELECT [Link], [Link] Dentist_Name,
[Link] || [Link] Patient_Name,
COUNT(ap.pro_ID) Procedure_Count,
SUM([Link]) Procedure_Total
FROM L4_Appoitnments a, L4_Dentists d, L4_App_Pros ap,
L4_Procedures p, L4_Pro_Dens pd, L4_Insurance_Cos i,
L4_Ins_Pros ip, L4_Patients pa
WHERE a.pat_ID = [Link] AND
a.den_ID = [Link] AND
pa.ins_ID = [Link] AND
pd.den_ID = [Link] AND
pd.pro_ID = [Link] AND
ip.ins_ID = [Link] AND
ip.pro_ID = [Link] AND
ap.app_ID = [Link] AND
ap.pro_ID = [Link] AND
(i.company_name = 'Manulife' OR i.company_name = 'Blue Cross') AND
[Link] > 1500 AND
[Link] = 'DDS'
-- if there are aggregate functions, group by everything in the select that
isnt an aggregate function
GROUP BY [Link], [Link], [Link], [Link]
ORDER BY [Link];
col Patient_Name format a25
col Dentist_Name format a25
set linesize 120
SYSDATE: built in function, returns a DATE value
captures the current date/time
SELECT SYSDATE FROM dual;
07-OCT-2024
-can do arithmetic with DATEs
suppose app_date is a DATE column
app_date + 1 -- the 1 represents a full day
returns a DATE exactly one day after app_date
app_date - 7 -- one week before app_date
app_date + 1/24 -- one hour after app_date
NORMALIZATION
Consider a cargo ship that has storage rooms for storage containers. Storage
containers are large
moveable containers that store individual packages to be shipped.
Package in a container in a room
-What if package p055 is removed from the boat?
Problem: there will be no record of Container 103. This is a deletion anomaly.
-What if a new empty container (say, 104) is added to Room B?
Problem: We cannot capture this if there is no package inside the container. This
is an insertion anomaly.
-What if we move container 101 from room A to room B?
We will have to update many rows for this single action. This is an update anomaly.
When there is an anomaly, there will be inconsistencies in the database.
FUNCTIONAL DEPENDENCY
Is a constraint about two or more columns of a table: X determines Y (X #-> Y) if
there exists at
most one value of Y for every value of X.
Suppose we have two columns A and B
We say A "determines" B (A -> B)
Package ID -> Container ID
can't go the other way since containers have more than one package
Normal Forms:
a way to describe a way in which a database has been fixed.
Different levels of normal form: First, second, third, BCNF (Boyce-Codd Normal
form)
Typically want third of BCNF
First (1NF): no multi-valued (repeating) attributes/ no repeating columns
Second (2NF): each relation (table) is in 1NF and has no partial functional
dependencies
Partial functional dependency: one or more non-key attributes are dependent on only
part of the candidate keys
Eg. candidate key: (a,b,c), partial functional dependency: b,c -> each
Third (3NF): each relation in the database in in 2NF and has no transitive
dependencies
Transitive Dependency: a functional dependency in which one or more non-key
attributes are dependent on determinant that includes a non-key attributes
Boyce-Codd NF: means that for any relation in a database, if FD X -> A, then A is
part of X or X is a super key
NORMALIZATION:
decomposing a large table into smaller tables
Ex. T(a,b,c,d,e,f)
1. c,d -> f
2. d -> a
3. d -> b
4. a,b -> e
5. d -> e
6. c,d -> a,b,e
Candidate key: (c,d)
c,d -> a,b,e,f
d -> a,b,e (all three (a,b,e) are partial dependencies, because the determinant (d)
is part of the candidate key (c,d))
a,b -> e
T(a,b,c#,d#,e,f) (# means key) start with the largest partial
dependency statement (the one with the most partial dependencies)
/ \
/ \ d -> a,b,e
T1(c#,d#,f) T2(d#,a,b,e)
/ \ a,b -> e
/ \
T3(d#,a,b) T4(a#,b#,e)
everything that isn't a dependent in the right table goes into left table
underline the foreign key constraint (T1 underline d#, T3 underline a,b(both
have same underline))
check that they are normalized (no partial or transitive funcional
dependencies)
T1 is normalized
T2 isn't normalized (transitive dependency)
T3 and T4 are normalized
Excersize:
T(a,b,c,d)
a -> c (not partial because a and c are both key attributes)
b,c -> d
c -> a (not partial same as a -> c)
a,b -> d
Candidate Keys: (a,b), (b,c)
Table has no transitive or partial dependencies, therefore it is in 3NF
But is it in Boyce-Codd NF? No, a -> c, a isn't a super key
T(a,b,c,d)
/ \
/ \ a -> c
T1(a#,b#,d) T2(a#,c)