Week 3 Flashcards
How is data represented in a relational data model
A collection of tables / a collection of relations
tables are called relations
What data is stored in rows
entities and relationships
What is in the column header
Attributes / attribute name
A row is also known as a …
tuple
A row contains an instance of what ever the relation name is . eg for a STUDENT (relation name) each row represents a specific student instance
bkjbjk
what does a key do in relational data model
Uniquley identifies a specific row ( and hence a specific instance as row = specific instance)
what is a domain
set of atomic (non -divisible) values allowed for an attribute
specifies the data type allowed for an attribute
what are surrogate / artificial keys
a unique identifier assigned to each record in a table,
eg I can increment studentID from by 1 each time to identify a student uniquely and that is a surrogate key
state
The content / data in the table at a particlular time
subset of the cartesian product of values in domain
tuples (rows) in a relational data model are…
unordered
For ease in the course , we will assume attributes are ordered as well
what does t[Ai]
for an arbituary tuple (row) t , the attribute denoted by the value of i
eg t[A2] the second attribute at the required tuple
What are the 3 main types of constraints in the relational data model
.Key constraints
.Entity integrity constraints
.Referential integrity constraints
implicit constraint is the domain constraint:
values in an attribute must be from the domain of that attribute
superkey info
a superkey is a set of one or more attributes (columns) that, taken together, can uniquely identify a tuple (row) in a table.
A key, on the other hand, is a minimal superkey, meaning it is a superkey with the fewest possible attributes. If removing any attribute from a key would cause it to lose its uniqueness, then that key is considered minimal.
Why is {serial no , Make} a superkey for a car
. At least one of the attributes can uniquely identify a car instance (tuple) [ serial no uniquely identifies}
However if you remove the make attribute it is still a superkey - Not minimal superkey - Therefore not a key
foreign key
. A foreign key is an attribute (or a set of attributes) in one relation (table) that establishes a link or reference to the primary key in another relation.
eg serial number ( primary key) for a CAR relation may link to a DRIVER relation
general rule of thumb - use the smallest sized key for primary key
What is a relational database schema
A set of the relation schemas
S = {R1,R2…Rn}
where R1,R2,…,Rn are names of the relations
Entity integrity
Ensuring values for primary keys are not null
If primary key contains several attributes, none of them can be null
Referential key integrity
The foreign key in one relation has to have the same domain as the primary key it refers to in another relation
Neso Academy Relational model constrains
A Value that occurs in the foreign key has to be present in the primary key
t[Fk] = t[Pk]
key constraint
No two tuples can have same value for a superkey / key( as a key is just a minimal superkey) since they uniquely indetify a tuple
(uniqueness is removed if more than 1 tuple has same value as you wont be able to identify)
. key must be a minimal superkey ( if you take an attribute away you can no longer uniquely identify)
what is a semantic integrity constraint
constraint specific to the database your building
eg: grades < = 100 (and greater than or equal to 0 )
