Lecture 4 - FDD Logical Database Design

integrity constraints

mechanisms for maintaining the integrity of stored data - domain constants - entity integrity - referential integrity (rules that maintain consistency between the rows of two related tables) rule states that any foreign key values (on the relation of the many side) MUST match a primary key value in the relation of the one side (or the foreign key can be null) - restrict, cascade, set-to-null

relation

named, two dimensional table of data consists of rows and columns (attribute or field) requirements: - must have a unique name - every attribute myst be atonic (not multivalued, not composite) - every row myst be unique - attributes (columns) in tables must have unique names - the order of columns and rows must be irrelevant

relational data model

no lines, just tables database model that presents data in 2-D tables using common data to link tables; connections used data called foreign keys (not pointers) so use common data to link tables

correspondence with E-R Model

relations(tables) correspond with entity types and with many-to-many relationship types - rows correspond with entity instances and with many-to-many relationship instances columns correspond with attributes the word relation (in relational database) is not the same as the word relationship (in ER model)

components of relational model

Data Structure Data Manipulation Data Integrity

key fields

special fields that serve two main purposes - primary (solid underline): unique identifiers of the relations; guarantees that all rows are unique, include employee numbers, ssn two primary keys combined are a composite primary key - foreign (dashed underlines): identifiers that enable a DEPENDENT relation on the many side of the relationship to refer to its PARENT relation (on the one side of the relationship) - can be simple or composite used as indices to speed up the response to user queries

logical base design

the process of transforming the conceptual data model into a logical model - one that is consistent and compatible with a specific type of database technology - creating stable database structures - correctly expressing the requirements in a technical language; our focus is on relational data model (day to day operations)