Create
Learn
Share

Databases- Relational Model

rename
ohsobeezee23's version from 2016-10-20 17:42

Section 1

Question Answer
Relational Databasea set of relations
Instancea table with rows and cols
#rowscardinality
#colsdegree or arity
SchemaBasic info describing a table or realtion
Normalization theoryhow to design relation schemas
How to create tableCREATE TABLE Students (sID CHAR(20),loginiD CHAR(10))
Create an enrolled relation based in Students relationCREATE TABLE Enrolled (sID CHAR(20),cID CHAR(20), grade CHAR(2))
DROP TABLEDestroys the relation. the schema info AND the tuples are deleted
ALTER TABLEAlters schema by adding a new column; every tuple in the current instance is extended with a NULL
example of alter tableALTER TABLE Students ADD firstYear INTEGER
DMLData manipulation Language
INSERT INTOTelling which table we are about to insert info to
SELECT, FROM ,WHEREusually the things following an INSERT INTO statement
how to delete all tuples say from a table which contains adamDELETE FROM Student S WHERE S.sName = 'Adam'
UPDATEChanges attributes of all tuples satisfying some conditions
UPDATE exampleUPDATE Enrolled E SET E.grade = 'A' WHERE E.sID = '3666' and E.cID ='comp3380'
memorize

Section 2

Question Answer
Integrity Constraints (ICs)conditions that must be true for any instance of the database
When are ICs specified ad checkedICs are specified when schema is defined and ICS are checked when relations are modified
legal Instanceone that satisfies all specified ICs
what are ICs used forensuring application semantics (e.g sId is a key) or to prevent inconsistencies
Types of integrity ConstraintsDomain, Key, Entity, Referential
Domain constraintsAttribute values must be of the right type, Always enforced
Key Constraints Forbid duplicate primary key value
Entity integrity constraints primary key is NOT NULL
Referential integrity constraints Ensure consistency of tuple references across tables (i.e, no dangling references)
memorize

Section 3

Question Answer
SuperKeyA set of one or more attributes that (taken collectively) identify uniquely an entity in an entity set
Candidate KeyMinimal superkey(i.e., a superkeysuch that none of its proper subset is a superkey) MORE THAN ONE CANDIDATE KEYS
Primary keyThe candidate key chose as the pricial means to identify entities in an entity set
UNIQUEPossibly many candidate keys one of which is chosen as the primary
Examle using uniqueCREATE TABLE Students (sID CHAR(20) NOT NULL, sName CHAR(20), loginID CHAR(10) NOT NULL, PRIMARY KEY(sID), UNIQUE(loginID))
Foreign keyset of attributes in one relation(i.e. referencing relation) that is used to "refer" to a tuple in another relation (i.e. referenced relation)
Foreign key examplesId is a foreign key referring to students Enrolled (sID: string, cID: string, grade: string)
memorize