1 / 6

6.830 Lecture 3

6.830 Lecture 3. Relational Algebra and Normalization Feb 13. Study Break # 1. Schema: classes: ( cid , c_name, c_rid , …) rooms: ( rid , bldg, …) students: ( sid , s_name, …) takes: ( t_sid , t_cid ). SELECT s_name FROM student,takes,classes WHERE t_sid=sid AND t_cid=cid

mitchell-jeremiah
Download Presentation

6.830 Lecture 3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 6.830 Lecture 3 Relational Algebra and Normalization Feb 13

  2. Study Break # 1 Schema: classes: (cid, c_name, c_rid, …) rooms: (rid, bldg, …) students: (sid, s_name, …) takes: (t_sid, t_cid) SELECT s_name FROM student,takes,classes WHERE t_sid=sid AND t_cid=cid AND c_name=‘6.830’

  3. Questions • Write an equivalent relational algebra expression for this query • Are there other possible expressions? • Do you think one would be more “efficient” to execute? Why? SELECT s_name FROM student,takes,classes WHERE t_sid=sid AND t_cid=cid AND c_name=‘6.830’

  4. Hobby Schema Table key is Hobby, SSN “Wide” schema – has redundancy and anomalies in the presence of updates, inserts, and deletes Entity Relationship Diagram SSN Name n:n Person Hobby Address Cost Name

  5. BCNFify Example for Hobbies Iter 1 S = SSN, H = Hobby, N = Name, A = Addr, C = Cost Iter 2 violates bcnf violates bcnf key Iter 3

  6. Study Break # 2 • Patient database • Want to represent patients at hospitals with doctors • Patients have names, birthdates • Doctors have names, specialties • Hospitals have names, addresses • One doctor can treat multiple patients, each patient has one doctor • Each patient in one hospital, hospitals have many patients 1) Draw an ER diagram 2) What are the functional dependencies 3) What is the normalized schema? Is it redundancy free?

More Related