1 / 6

Exploring Relational Algebra and Normalization in Database Theory

This lecture focuses on relational algebra and normalization, examining a SQL query that retrieves student names enrolled in the course '6.830'. It includes questions about equivalent relational algebra expressions, possible variations, and execution efficiency. The session also discusses a hospital patient database schema, requiring the creation of an ER diagram, identification of functional dependencies, and evaluation of normalization and redundancy. Understanding these concepts is crucial for efficient database management and design.

mitchell-jeremiah
Télécharger la présentation

Exploring Relational Algebra and Normalization in Database Theory

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