1 / 19

Relational Databases:

Relational Databases:. 2 - Dimensional tables/relations e.g: Student: Relational Operators Select - subset of rows from a table Project - subset of columns from a table Join - Create new table by linking two tables on common attributes Duplicate Rows not allowed ( Primary key )

amena-hunt
Download Presentation

Relational Databases:

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. Relational Databases: • 2 - Dimensional tables/relations e.g: Student: • Relational Operators • Select - subset of rows from a table • Project - subset of columns from a table • Join - Create new table by linking two tables on common attributes • Duplicate Rows not allowed ( Primary key ) PK : Attribute(s)that uniquely define each row Cannot have missing (Null) PK value St# Name Classfication

  2. Student Department • Foreign Key • Defines relationship between entities e.g S# Dept# Sname DName Major Daddress Student: Major S# Name Dept#

  3. Dept# DName DAddress Columns whose values point to PK values of another table are FKs Department: RULE: • FK values either reference existing PK values or they are NULL ( Referential Integrity Constraint)

  4. Order# O-Date Prod# Prod_Desc Qty-Ordered 20 21 9/30/97 9/30/97 304 298 304 10 5 Normalization: • optimizes relational DB design • Removes potential problems/anomalies e.g Consider: Order Sales Rep Product Lists Stored in Serves Places Warehouse Customer Order

  5. Repeating Group Order ( Order#, O-Date, (Prod#, ProdDesc, Qty)) First Normal Form: • No repeating groups Order ( Order#, O-Date, Prod#, ProdDesc, Qty) Potential Problems: • change a particular products description • can have inconsistent data • adding a product when an order does to exist for it • deletions Second Normal Form: • in 1NF • all non-key attributes are dependent on the entire PK 1NF

  6. To convert 1NF to 2NF: • Separate table for each field in PK • Separate table for all combinations of PK fields, taken 2 at a time, 3 at a time etc. e.g. Order ( Order#, Odate) Product ( P#, Pdesc ) OrderLine ( Order#, P#, Qty-Ordered ) Consider Customer ( Cust#, Cname, CAddr, SalesRep#, SRName) • in 1NF ( no repeating groups) • in 2NF ( PK is a single field)

  7. Problems • changing a SalesRep’s name • adding a SalesRep who does not yet have a customer • deletions Third Normal Form: • in 2NF • no non-key field is dependent on another non-key field To change to 3NF: • remove all fields that are dependent on another non-key field and place in new table with the other field as the key e.g. SalesRep ( SalesRep#, SRName ) Customer ( Cust#, CName, CAddr, SalesRep# (FK))

  8. Boyce - Codd NF • 4NF • 5 NF

  9. Example: Course and Instructor: Course ( Course #, CTitle, Inst#(FK) ) Student and Course: EnrollsIn (S-ID, Course#, Grade ) Student and Major: StudentMajor (S-ID, Major) Student and CampusOrg: BelongsTo (S-ID, Std-Org) Student(…),Instructor (….) S-ID SName HomeAddr Campus Addr Student Major Major Course Campus Org Std-Org Course# CTitle Inst# InstName Office Phone Instructor

  10. Logical Data ModelPhysical Data Model Attribute Data Element, Field Object Class File, Table, Relation Object Instance Logical Record Identifier Primary Key Relationship Foreign Key O-R Model Database

  11. Modeling relationship between Two entities: Rule1:For 1:1 (Cardinality) - place PK of one rec-type) in to rec-type for another Rule2: For 1:M or M:1 - place PK of rec-type for the entity on the 1 side into record-type for the entity on the M side Rule3:For M :M- create a new record (Associative Entity ) with PK composed of PKs of the two entities, and place ant attribute that is determined by both PKs into the new rec-type. Rule4:Discard rec-type with only one data element. This has already been included in other record-type by above rules

  12. Ternary Relationships ( When relationship between at least two of the entities is M:M) Case1: All relationships are M:M • Form new rec-type with PK consisting of PKs of all 3 entities. Place any data element determined by all 3 PKs into new rec-type. Case 2: M:M and M:1 • Form new rec_type with PK consisting of PKs of the M:M entities. Place PK of entity on the 1 side of relationship into this new rec-type as a non-key element

  13. S-ID S-Name S-Addr Ternary Relationship Example: Students may have more than one Major and for each major, a student is assigned an advisor Student ( S-ID,S-Name, S-Addr ) Advisor ( Ad-ID, Ad Name, Ad Phone) Major ( Major ) Std- Major- Advisor (S-ID, Major, Ad-ID ) Student M Has 1 Ad-ID Ad-Name Ad-Phone Advisor Major Major M

  14. Primary Physician Patients PatientID P-Address P-DOB P-Hphone P-Wphone P-Sex P-Occupation Physician Phy Name PhySpeciality PhyPhone Phy-Pager Appointment App Date App Time Complaint Hospital HospName

  15. Ex: Patient Records System: PATIENT (PatientID, P-Name, P-Addr, PDOB,P-HomePhone, P-WPhone,P-Sex,P-Occupation, PhyName(FK)) PHYSICIAN (PhyName, PhySpeciality, PhyPhone, PhyPager, HospitalName(FK)) HOSPITAL (HospitalName) APPOINTMENT (PatientID, PhyName, ApptDate, ApptTime, Complaint) ASSUMPTION: Patient does not have more than one appointment with same physician on the same day.

  16. DATA MODELLING IN LIFECYCLE PHASES: System Planning • Study (Business Mission ) • No data modeling; but certain critical data entities may be identified • Definition Enterprise Analysis - Enterprise Data Model ( Objects) • Mgmt’s high level view of critical entities Business Area Analysis • More detail - attributes, relationships cardinalities (may entail changes to enterprise data model )

  17. Systems Analysis • Preliminary Investigations • Application context data model • Study of current system • Focus on process modeling • Requirement Analysis • Expand and refine data model • Application essential Data Model ( Implementation independent) System Design Implementation Data Model (Files, DBs, normalization,etc)

More Related