logical design/ relational schema

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 associative entity

mapping weak entities

• primary key has 2 parts:

  • Partial identifier + the primary key of the strong entity

mapping ternary and n-ary relationships

• The associative entity will have the primary key ef 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

• candidate for the unique identifier

  • 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

  1. Remove multivalued attributes

     1. 1NF

     2. Every attribute will have its own line

  2. Remove partial dependencies

     1. 2NF

     2. AKA removing many to many relationships

     3. Many to many relationships makes the non key attribute defined by only part of the key

  3. Remove transitive dependencies

     1. 3NF

     2. AKA removing any one to many relationships

     3. to remove functional dependencies between 2 + non-key attributes

  4. Remove remaining anomalies due to there being multiple candidate keys

     1. Boyce-Codd normal form

  5. Remove multivalued dependencies

     1. 4NF

  6. Remove remaining anomalies

     1. 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