Unit 3 - Relational Model Flashcards
(34 cards)
Data model
Collection of high-level data description constructs (e.g., tables and links) that hide many low-level storage details
Relational model
Data is in table(s) (aka RELATIONS, not relationships)
What do rows and columns of relation represent?
Rows are tuples or individual records and columns are attributes (or fields)
Can two relations have the same name?
No, name of relation must be distinct
Can two attributes have the same name if they’re in different relations?
Yes.
In the same relation, each attribute must have a distinct name (we cannot have two or more attributes with the same name)
However, two attributes in different relations can have same name.
Can a cell in a relation have a composite value?
No - each cells contains exactly one atomic (single) value
Can you have different types of values in a column?
No - values of an attribute are all from the same domain
Can you have two duplicate tuples?
No - no duplicate tuples.
What order must the attributes be in?
None. Order of attributes has no importance.
What order must the tuples be in?
None. Tuples can appear in any order (but order may impact efficiency)
What’s a schema?
Header of table: relation’s name, name of each field/attribute, domain of each field
e.g., Students (sid : string , name : string , age : integer , gpa : real)
From ER to relations - Step 1: Translate Entity Sets to Relations Steps
Step 1: each atomic attribute of an entity set becomes an attribute (field) in the relation schema; entity set name = relation name; underline primary key
How do you translate a composite attribute in an ER diagram to schema?
Concatenate the root attribute + composite attribute (order of concatenation doesn’t matter) and that becomes the attribute/field
e.g., let’s say Name is composite and its atomic attributes are First and Last. The employee relation is,
Employee (SSN : integer, First_Name : string, Last_Name : string)
What do derived attributes look like in a relational schema?
They are NOT included
What if there’s a multi-value attribute in the ER? (e.g., multiple email addresses associated with an employee)
You should break that table into two tables:
1. original table minus the email column, and
2. each email address + identifier
When we create more tables from an existing table, what becomes the primary key in the new tables?
Usually it’s a concatenated key (e.g., Skillset + SSN), but remember one of the rows is primary key from the previous table (so it’s a FOREIGN key)
Don’t forget to add an arrow from foreign key to primary key!
What are the steps to translate entity sets to relations (tables)?
Step 1: place all single-value attributes in the main relation
Step 2: for each multi-value attribute, create a new relation; determine the composite primary key in new relation (multi value attribute is partial key + primary key from prev table is foreign key)
Step 3: Add an arrow from foreign key (of the new relation) to primary key (of the main relation)
How to translate relationship set to relation?
Create schemas, then add the primary key of one of the schemas as a foreign key to the other schema
How to translate M:N relationship set to relation?
Create a third schema/table that represents the relationship set - its composite primary key will be the primary keys of the two schemas.
Example: you have entity set S, relationship set ‘makes’ and weak entity set T.
What is the primary key for weak entity set T?
It’s a composite primary key:
Primary key S + primary key of T
^ this is foreign key of T (from S)
What are the three main problems of data redundancy?
Insertion, Update, Deletion
^ Anomalies
What is Insertion Anomaly with data redundancy?
For all 1000 records, the same data must be input correctly 1000 times.
What is Update Anomaly with data redundancy?
If one field changes, and there are 1000 records, must update it 1000 times.
If we miss one, data inconsistency.
What is Deletion Anomaly with data redundancy?
Let’s say each record has info on the student and the department.
If the last student is deleted, we lose all stored info about the department!
