Relational Data Model Flashcards
What is a relational database?
A set of relations
What are the two parts of a relation?
Instance (table) and Scheme (name, plus attributes)
What is the cardinality of a relation?
Number of Rows
What is the degree of a relation?
Number of columns
What is the DDL used for?
Creation, Deletion, Updating, Truncate, Rename relation
What is the simple form of creating a relation?
CREATE TABLE ( ssn INTEGER );
What are the 4 most common data types?
INT, REAL, CHAR(n), VARCHAR(n)
What is the form of the data value?
yyyy-mm-dd
What is the form of the time value?
hh:mm:ss.ss
What is the syntax to delete a relation?
DROP TABLE ;
How do you add any existing attribute to a relation scheme
ALTER TABLE ADD
How do you remove an attribute from a relation?
ALTER TABLE DROP
How do you insert a single tuple?
INSERT INTO Students(sid, name) VALUES (53, ‘Smith’)
Do you delete all tuples satisfying some condition?
DELETE
FROM Students S
WHERE S.name = ‘Smith’
What are integrity constraints?
Conditions that must be true for any instance of the database
When are ICs specified?
When schemes are defined
When are ICS checked?
When relations are modified?
How are keys declared?
Using the PRIMARY KEY or UNIQUE keywords after a single attribute or after all attributes using PRIMARY KEY(attr1, attr2)
What’s the difference between PRIMARY KEY and UNIQUE?
Exactly 1 PK, but 0+ UNIQUE
NULLS not allowed in PK, but UNIQUE can have NULLs
How do you force a value to exist and enforce total participation?
NOT NULL
What is a foreign key?
A set of fields in one relation that is used to refer to a tuple in another relation
How do you specify FOREIGN KEY?
FOREIGN KEY (sid) REFERENCES STudents
What are the options for foreign key violations?
NO ACTION, CASCADE, SET NULL, SET DEFAULT
Ex ON DELETE CASCADE
ON UPDATE SET DEFAULT
How to convert an entity to a relation?
CREATE TABLE ( PRIMARY KEY );
How to convert a relationship to a relation?
Attributes must include the keys of each entity and any additional descriptive attributes. Add foreign key for the keys of the entities
How to convert Many to One relationships to relations?
Combine both entities into the “many”;
Ex: Given many drinkers and 1 favorite beer:
Drinkers(name, addr, favBeer)
Can we combine many-many relationships?
No, creates much redundancy
How to convert a weak entity set to relations?
Combine the relationship and weak entity into one relation, adding the foreign key of the supporting entity as a relationship
How do enforce total participation?
use NOT NULL (especially on foreign keys)
What are the 3 approaches to subclasses?
- Object Oriented: one relation per subset of subclasses with all relevant attributes
- Use nulls: one relation, use NULLs in attributes that don’t belong
- E/R: one relation for each subclass (key attributes, attributes of that subclass)
What is a view?
A relation that stores a definition, rather than a set of tuples. Used to present necessary information while hiding details of the underlying relations (security)
