DSO 428 - Quiz 2
Practice Example Pt 1
Dno / EMPLOYEE Super_ssn / EMPLOYEE Mgr_ssn / DEPARTMENT Dnumber / DEPT_LOCATION
What would happen to staff in B003 if the branch is renamed? Update B003 to B001
What will happen to all the records in Staff table with B0003? 1. You would specify the referential integrity CASCADE (update branch number for all staff - don't fire because branch still exists its just renamed) 2. SET NULL (set all staff to null with B003 until they're reassigned) 3. Cannot update branchno e.g. bcard (NO ACTION)
Practical Analysis - 2 Step Check
1. Can't insert b/c violates entity integrity (primary key can't take null value) 2. Can't insert b/c CR74 already exists in table
Properties of Relations
1. Relation name is distinct from all other relation names in relational schema. - If you have multiple tables, there shouldn't be two tables with the same name - Can't talk to the database and call the right table if there are duplicate names 2. Each cell of relation contains exactly 1 atomic (single) value. 3. Each attribute has a distinct name. 4. Values of an attribute are all from the same domain. 5. Each tuple is distinct; no duplicate tuples - Impose these constraints to make sure we can retrieve the info in the future (no duplicate records to call on) - If a certain use calls one of two rows there's no way they can differentiate between the two rows (same content) - Even if you're entering names and two people have the same name → there will be different IDs attached to these rows 6. Order of attributes has no significance. 7. Order of tuples has no significance, theoretically
Relational data model consists of three parts
1. Relations - there are two tables 2. Relationship between two tables (presented using structure called "primary key" / "foreign key" paid) 3. Integrity Constraints (rules to govern dynamic updating process in database) BranchNo is the index column This index column is copied to the second table (BranchNo appears twice) BranchNo is called primary key in first table BranchNo is called foreign key in second table Can use BranchNo to combine two tables and access/refer to data across tables This is done through primary key / foreign key connection
Practical Analysis Part 2
1. We CAN insert b/c foreign key (branchNo) can take null value in home table AND because SG82 is not a repeat in home staffNo table 2. Can't insert b/c B001 doesn't exist in primary key home table --> violates referential integrity 3. We can't insert because entity integrity (can't insert null into primary key) 4. We can't insert because not unique
Examples for Properties of Relations
1. You cannot insert this record because it's an exact duplicate 2. You can insert this record because branchNo primary key is not a duplicate! 3. You can't insert because it's an exact duplicate 4. Atomic (not a single cell value) - Can't insert record with a cell that contains more than one value (contains both Manager and Accountant) - How can you do this? → create a new table! --> List everything you already have in two new rows (same info but two positions) --> You could also create two columns (position 1, position 2 **could have a lot of empty cells for those who don't have 2 positions)
1st Scenario
1st scenario: Could change BranchNo for these SG to null until you decide what branch they're re-assigned to - Set Null (allow uncertainty)
2nd Scenario
2nd scenario: Could delete SG ones (fire all staff) - Cascade Upstream change will be propagated to downstream tables (cascading down) You would delete all B0003 in Staff table (SG37, SG14, and SG5) aka fire or get rid of them
3rd Scenario
3rd scenario: You just CAN'T delete a record if you haven't found a good solution for these SGs - No Action Can't close branch before new allocation Find out a way of how to change value in Staff table (replace B0003) before you delete the record Don't assume deleting these records can just be executed
General Constraints Example
Additional rules specified by users or database administrators that define or constrain some aspect of the enterprise. (e.g. staff in a branch<20) Can you insert B0009 (new branchNo) into Staff table? → NO - Violates referential integrity b/c not in home table - If you add B0009 into Staff table and not the Branch home table you will Null value in your Staff table (b/c not existing in home branch table) - Would have to insert B0009 into Branch home table before inserting into Staff table - However, you CAN insert new staff into Staff table that does have a null branchNo (staff isn't assigned to branch yet but will be assigned to one of the branchNo already existing in the Branch table)
Foreign Key
An attribute or set of attributes within one relation Matches the candidate key of some (possible same) relation I.e. foreign key references primary key - Relationship between focal table and another table UID student table (focal) and Course Registration table (contains UID, course id, semester, etc.) - These two UIDs are connected - UID in Course Registration table is FOREGIN KEY - UID in Student Table is PRIMARY KEY - Connect two tables by FK / PK! Construct relationships between tables through FK / PK!
Super Key How to ID a Super Key?
Attribute or set of attributes (column) in a relation Uniquely identifies a tuple (row) within the relation --> Uniquely identify ANY student in the table or ANY row in the table How to ID a Super Key? 1. Data Dictionary / System Catalog 2. Common Sense / Semantic Meaning Example: Is UID a superkey to uniquely identify any student in our table? Yes → everyone is assigned a unique one (different for everyone) Is SSN a superkey to uniquely identify any student in our table? NO! Not good to use for privacy purposes Some people might not have it (only U.S. citizens) Is UID + SSN a superkey? Yes because UID is already a superkey But don't need SSN Is Name + GPA + Degree + Grad_Yr a superkey? No → there could still be duplicates Not a guarantee
Exercise: ID Primary and Foreign Keys
Branch (branchNo) Staff (staffNo, / branchNo/ ) --> branch could be assigned to multiple staff PropertyForRent (propertyNo, /staffNo/, /branchNo/, /ownerNo/ ) - Why does it have so many foreign keys? 1 to many relationship - One branch may maintain multiple properties - One stay may maintain multiple properties Client (clientNo) PrivateOwner (ownerNo) Viewing (/clientNo/, /propertyNo/) → if you can visit same property multiple times in one day you need to add viewDate to primary key Registration (/clientNo/, /branchNo/, /staffNo/ ) - If company doesn't have a centralized database then the client would be registered multiple times / separately in different branches - In this case, each branch would maintain their own data (client would be registered multiple times) → clientNo is not enough to uniquely identify registration - If the company did have a centralized database and clientNo was a primary key...you could easily just integrate columns of branchNo, staffNo, and date_joined to Client table and condense tables! - You can integrate these tables b/c they would share the same primary key - Since these tables are separate, that's our HINT that the primary key for Registration has to be more than just clientNo Takeaway: With such a small sample in this table we can't determine the primary key every time (could be both options) --> We need to understand the business model
Three step procedure: to identify Super/Candidate/Primary Key/Alternative Key
Can you use the set of attributes to uniquely identify row in table? UID + FB_ID → yes Can you further reduce that superkey but still uniquely identify the row in table? Yes → can remove FB_ID and becomes Super Key If no...becomes Candidate Key Super Key --> Candidate Key (condensed form) --> Primary Key vs. Alternate Key UID + FB_ID → is a Super Key but not a Candidate Key - Once you condense it to UID it will be a Candidate Key!
Practice Example Pt 2
Dnum / PROJECT Essn / WORKS_ON Pno / WORKS_ON Essn / DEPENDENT
Practical Analysis of Constraints (2 Step Check!)
EMPLOYEE (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, /Super_ssn/, /Dno/) DEPARTMENT (Dname, Dnumber, /Mgr_ssn/, Mgr_start_date) DEPT_LOCATIONS (/Dnumber/, Dlocation) PROJECT (Pname, Pnumber, Plocation, /Dnum/) WORKS_ON (/Essn/, /Pno/, Hours) DEPENDENT (/Essn/, Dependent_name, Sex, Bdate, Relationship)
Exercise: FK and PK Answer
First search Primary Key and then search for matching Foreign Key!! Employee: Super_ssn can't be unique b/c one supervisor could manage multiple employees --> one supervisor could have multiple employees (many to one, super_ssn is not unique in employee table b/c could reference many employees) Department: Mgr_ ssn is a foreign key but it's a subset of employee SSN (still satisfies definition of foreign key) --> one manager number could mg multiple departments - Foreign key: one attribute or a set of attributes that match the value of a primary key in one table - Manager ssn may take 10/100 values in employee ssn but it still matches so it's still a foreign key - Manager ssn foreign key that reference back to employee table Dept _ location: Dlocation can't alone work because the same building might host multiple departments! Dnumber alone won't work because one number could reference six different locations No foreign key for dept_location (no couple match of composite primary keys) Works_On: Could be multiple project numbers coordinating to one employee's SSN - An employee works on project → so you need a composite primary key (two primary keys) Dependent: Dependent_name alone is not enough - Some dependent for different employee SSN can have the same name
Referential Integrity
If foreign key exists in a relation, either foreign key must 1. Match a candidate key value of some tuple in its home relation, OR... - i.e. can't enter a foreign key that doesn't exist in primary key tuple of home table 2. Foreign key value must be wholly null - i.e. can enter null value into foreign key if doesn't exist yet in primary key of home table or unsure of value
Entity Integrity
In a base relation, no attribute of a primary key can be null (don't forget another constraint) - Primary key can uniquely identify a tuple - You can enter null into GPA and Name tuples (because these aren't primary keys) but not SSN tuple
Integrity Constraints
Integrity Constraints → when we update one table how does that impact other tables / update the other datatables? Null: Represents value for an attribute that is currently unknown or not applicable for tuple (e.g. survey → some people don't have answers to some questions and skip through them Deals with incomplete or exceptional data (unknown) Represent the absence of a value and is not the same as zero or spaces (zero or spaces are values) --> SSN won't work anymore as the primary key if null values are entered!
Primary Key Alternate Key
Primary Key (PK) The chosen / selected candidate key (UID) to identify tuples uniquely within a relation - Primary key can be one column or could be a combination of multiple columns Alternate Keys Candidate keys that are not selected to be primary key (SSN)
Primary/Candidate Key vs. Foreign Key
Primary/Candidate Key - Super key + Cannot be further reduced - We use PK to uniquely identify any record within a relation Foreign Key - An attribute or a set of attributes that matches PK/CK in some (possibly same) relation. - We use FK to get information from some (other) relation FK-PK is the only right way to join information from another table (not through any other set of attributes) We can find FK in a database by 1) first identifying PK within each table; 2) search FK in other relations FK can also match PK of the same relation (don't forget to search FK within the same relation)
Referential Integrity in Delete / Update action What would happen to staff in B003 if the branch is closed ? (delete a row in Branch)
Referential Action → Property to protect foreign key from changes in primary key in home relation Says how the foreign key is affected when you change B003 in home/upstream table If we were to remove B003 in the Branch table, how would we handle SG37, SG14, or SG5 in the Staff table? Referential Entity (action) will define what will happen to BranchNo in Staff table when you delete B0003 in home table Will say → "On Delete No Action" (default choice), "On Delete Set Null" or "On Delete Cascade" Referential Actions is about How FK is affected, when there is change in PK - go to see rules
Relations: Terminology Relation Attribute Tuple Degree Cardinality Domain What is the degree and cardinality of this table?
Relation = Table with columns and rows Attribute = named Column of a relation Tuple = row of a relation Degree = Number of Attributes or Columns in a relation Cardinality = number of records, rows, tuples in a relation Domain = set of allowable values for one or more attributes - Each column is defined on a domain - Set of all possible values for an attribute (column) - If domain name is Branch Number...the character should only have a size of four (B005) - This Branch Number must call into this character for the domain Degree of top table: 4 (4 columns) Cardinality: 5 (top table) → don't count header row
Simple Example
Rules: Can have same fname Can have same lname Not same exact fname + name (UID) - superkey - candidate key - primary key (fName) (lName) (fName,lName) - superkey - candidate key - alternate key (UID, fName) - superkey (UID,lName) - superkey (UID,fName,lName) - superkey If you only have one candidate key it will automatically be your primary key
Candidate Key
Super key (uniqueness) - A refinement of superkey - Should not contain any redundant attribute - Can't reduce it any more (simplest form of superkey) No proper subset is a super key for the relation (irreducibility) - Cannot use a proper subset to uniquely identify a record in the table May have more than one attribute - composite key - Composite Key = more than one primary key A relation may have multiple candidate keys Example: Is UID + Degree a candidate key? No → not concise enough yet (you can use the UID alone and make that the candidate key) If you cannot further reduce it then it's a candidate key In this case we can still remove the degree part and use UID alone If a superkey has only one attribute, is it automatically a candidate key? YES A super key can have multiple candidate keys UID and SSN can both be candidate keys but which one is better? → UID From a math perspective both columns can satisfy the property But from a privacy perspective all of us want UID
What if you want to delete records in downstream table instead? (delete SG14 and remove B0003 from Staff table)
Will that affect the B0003 in the upstream table? (Branch table) No! Removal of record in downstream table won't affect upstream table Removal of branchNo in upstream table will have a much larger effect on the downstream table! → you must set BranchNo records in Staff downstream table to null or delete them before you can B0003 in upstream Start building upstream table before downstream table so that you can embed the primary key in the upstream table as a foreign key in the downstream table
Why is the branchNo the foreign key?
branchNo can match multiple staffNo (so not unique in Staff table and therefore foreign key) Depends on the mapping of the records for each specific data table Branch and Staff have one to many relationship One branch member may correlate to multiple staff members The ONE table always has the priority! (only one branchNo in Branch table but multiple branchNo in Staff table) The table with less records will take the priority
Self Targeting (recursive): Foreign Key:
relationship within a table set of attributes within a relation that matches the candidate key of some (possibly same) relation Super_ssn is a foreign key that corresponds to Essn
