relational database

Question 1

what are relations made up of

Answer 1

an instance and a relation schema

Question 2

instance

Answer 2

a table with rows and columns

Question 3

relation schema

Answer 3

specifies the relations name and type of each column

Question 4

cardinality

Answer 4

number of rows

Question 5

degree/arity

Answer 5

number of fields/attributes

Question 6

contraints

Answer 6

logical rules that establish the integrity of data

Question 7

integrity constraints

Answer 7

a condition that must be true for any database instance

Question 8

legal instance

Answer 8

satisfies all specified integrity constraints

Question 9

what are the 3 types of integrity contstriants

Answer 9

domain constraints
entity integrity constraints
referential integrity

Question 10

domain constraints

Answer 10

the value of the attribute must be an element of the attributes domain

Question 11

entity integrity constraint

Answer 11

a key value cannot be duplicated or left empty

Question 12

what does referential integrity do

Answer 12

connects tables together through a foreign key

Question 13

what is the role of a foreign key

Answer 13

a peripheral attribute that creates referential integrity between entity sets

Question 14

in which two ways can foreign keys be made

Answer 14

importing the primary key from one table to another
creating a new table that holds the primary key of both tables

Question 15

what are some constraints for foreign keys

Answer 15

they must have the same name and domain type as the referencing relation
related entities must have the same value

Question 16

how do we derive a foreign key for 1:1 relationships

Answer 16

import the primary key from one table to the other and declare it as a foreign key

Question 17

what is the difference between deleting a tuple using SET DEFAULT and SET NULL

Answer 17

default; removes the current foreign key and leaves a placeholder
null; completely removes the relationship

Question 18

how do you delete an entity from a referenced table

Answer 18

get the primary key value of the table
find all the tuples with the fk in the referencing table and select them
set the fk to the default value or null (if null delete all tuples in the referenced table)

Question 19

how do you derive a fk 1:1 both partial participation

Answer 19

pk from one table becomes the UNIQUE fk for the other

Question 20

how do you derive a fk 1:1 both total participation

Answer 20

can combine into a single table and include all attributes choosing which pk to stay as the pk
or you can keep them separate and make one the NOT NULL and UNIQUE fk

Question 21

how do you derive a fk 1:1 only one total participation

Answer 21

add the not null and unique fk to the totally participating side
on delete cascade/reject

Question 22

what is the difference between on delete cascade and reject

Answer 22

cascade; when removing the fk in its table you have to remove the related entity
reject; you cannot remove the fk as there is a related entity

Question 23

how do you derive a fk 1:M both total participation

Answer 23

combine the relationship with the many entity set
the pk from the 1 side becomes the fk (! null)

Question 24

how do you derive a fk M:N both total participation

Answer 24

create a relationship set and create the schema for it
use both pk to create a composite primary key
must delete on cascade

Question 25

composite primary key

Answer 25

two pk joined together that allows for combinations

Question 26

how do you derive a fk from a weak entity set

Answer 26

combine the relation with the weak entity and use the string pk as the fk the pk is a composite of the strong pk and the weak partial key delete on cascade

Question 27

how do you derive a fk 1:M both partial participation

Answer 27

join the relational set to the many side with the 1 side pk as a fk