Studied by 0 people
Looks like no one added any tags here yet for you.
relational model
structure
Tables
Keys
Primary or foreign
uses SQL for retrieving and modifying data
A way to implement business rules
which maintain the integrity of manipulated data
keeps data accurate and consistent from the point of creation to processing
relation
A 2 dimensional table of data
entity
Unique Name
Rows
Unique Records
Two rows cannot have the same information across all attributes
Order does not matter
Columns
Attributes or fields or alias
Need to have unique names
Atomic
Not multivalued or composite
Order does not matter
logical design
database design that maps conceptual requirements
keys
Have different purposes
Primary
Unique identifiers
Guarantees that each row will be unique
Cannot be duplicated or empty
Can be simple or composite
One attribute or more than one attribute (for associate entities)
Foreign
Allows a dependent relationship to occur
An entity on the many side of a relationship is able to refer to its parent which is on the one side
Used as indexes to speed up the response to queries
For example
Yellow Pages
If you have the address then you can connect it to a name or an e-mail for example
constraints
Domain constraints
The allowable values for an attribute
Minimum and maximum values allowed
consists of:
size
domain name
data type
Entity integrity rule/constraints
Primary keys cannot be empty and they need to be unique
referential integrity constraints
Maintains consistency between the rows of two related tables
If you have a value for a foreign key, then the instance that it points to needs to exist
fk needs to match the corresponding pk
To ensure
Restrict
Don't delete the parent side
Cascade
Automatically delete the dependent site if the parents side is deleted
This is how you protect weak entities
Set to null
If you delete from the parents side then make sure that the foreign key on the dependent side is null
how to transfer ERD into relational
Simple attributes can be mapped directly onto the relational database
Composite attributes can only use their simple component attributes when mapped onto the relational database
Multivalued attributes need their own new entity
mapping binary relationships
Where do the primary and foreign keys go?
to connect entities
You need to create a foreign key attribute based on the other entities primary key
One to many
One side = primary
Many side = foreign
one to one
It can go on either side unless if you are dealing with an optional mandatory relationship
Mandatory = primary
Optional = foreign
Many to many
You need to create a new entity (associative) with the primary keys of those two entities as its primary key
You need to add the relationships attribute to this new entity
If an identifier is assigned, use that as the primary key
The primary keys of the parent entities will become partial identifiers
Should be natural and familiar
The default identifier may not always be unique
mapping unary relationships
One to many
A recursive foreign key in the same relation
Many to many
You will need 2 entities
One for the entity type
One for the associative entity
The primary key will be the primary key of the entity type as well as the recursive foreign key
mapping weak entities
primary key has 2 parts:
Partial identifier will become a primary key + the primary key of the strong entity
mapping ternary and n-ary relationships
The associative entity will have the foreign key to each entity in the relationship
mapping specialization relationships
One to one relationship
Subtype will have the optional side and the foreign key
The foreign key will be the first letter of the subtype entity along with the full name of the primary key of the Super type
functional dependency
The value of an attribute determines the value of another attribute
a constraint between 2 attributes
partial functional
non key attributes are functionally dependent on only part of the key
candidate key
A unique identifier
Sometimes there can be multiple
One of them is chosen to be the primary key
does not indicate a row’s position in the table
Every attribute that's not a key is functionally dependent on every candidate key
data normalization
A way to improve the logical design so that there's no unnecessary duplication of data
easier to maintain data
Decomposing entities with anomalies to produce smaller, well structured, entities
Minimal data redundancy
Users can insert, delete, update rows without causing any inconsistencies
Types of anomalies
Insertion anomaly
When a user adds new rows, they create duplicate data
Deletion anomaly
Deleting rows with data that will be needed for other future rows
Modification anomaly
Changing/editing data in one row forces the changing of data and other rows because of duplication
Removing redundancy without losing relationships
end goal: A table should not pertain to more than one entity type
Steps
Remove multivalued attributes
1NF
Every attribute will have its own line
Remove partial dependencies
2NF
AKA removing many to many relationships
Many to many relationships makes the non key attribute defined by only part of the key
Remove transitive dependencies
3NF
AKA removing any one to many relationships
to remove functional dependencies between 2 + non-key attributes
Remove remaining anomalies due to there being multiple candidate keys
Boyce-Codd normal form
Remove multivalued dependencies
4NF
Remove remaining anomalies
5NF
enterprise keys
If you have lots of entities, you want to create an object table that will provide a primary key for all of the entities
when to use sample data to create an instance
to improve user comms.
to check accuracy of design
for prototype generation
determinant
in a functional dependency, attribute on the left is the determinant
determines the value of of the attribute on the other side of the functional dependency
