Database Concepts

1
Database Concepts

• Logical Database Design

2
Logical Design

• Logical design is in the middle of the systems
  development life cycle.
• After analysis and before the physical design.
• The goal is to produce a detailed design
  specification so physical development can occur
  efficiently.

3
The Relational Data Model

• The relational data model was introduced by E. F.
  Codd and is the dominant technology for database
  management.
• Your experience with Access should make this
  concept relatively easy to learn.

4
Basic Terms

• Data structure
• Data is organized in the form of tables with rows
  and columns.
• Data manipulation
• The stored data can be manipulated using SQL
• Data integrity
• established business rules are not broken by
  adding new data or manipulating existing data

5
Relational Data Structure

• A relation is a two-dimensional table of data.
• Columns are identified by an attribute (field).
• Each row corresponds to a record for a single
  entity

6
Relational Keys

• A primary key is an attribute or combination of
  attributes that uniquely identifies each row in a
  relation.
• The pub_id is the primary key for the publisher
  relation, and can be used to refer to a specific
  publisher.

7
Relational Keys (cont.)

• Composite key
• A primary key that consists of more than one
  attribute. The product_id of a product may made
  up of the plant_id where it was made and the date
  it was made.
• Foreign key
• An attribute in one table that is a primary key
  in another table, and used to establish a
  relationship between the two tables.

8
Properties of Relations

• Each table has a unique name.
• Each entry in the table is atomicyou cant have
  two pieces of data in one field.
• Each row is unique.
• Each attribute within a table (and perhaps the
  database) has a unique name.
• The sequence of columns from left to right is
  insignificantthe order doesnt affect the
  relationship.
• The sequence of rows from top to bottom is
  insignificant.

9
Integrity Constraints

• Domain constraints
• the allowable values for an attributenumbers,
  text (character size), objects, etc.
• Entity integrity
• No primary key attribute may be null (empty)they
  must contain data.
• Action assertions
• Business rules--A movie can be rated G, PG,
  PG-13, R, NC-17, UR.

10
Referential Integrity

• Referential integrity is a rule that states that
  a foreign key value in one table must match a
  primary key value in the related table.
• Example The table that makes up the enrollment
  data for students must use PIDs for students that
  are in the students table.
• Restrict cant delete a parent row if the
  primary key value is used in a dependent row.
• Cascade If a parent row is deleted, all
  dependent rows in related tables are also
  deleted.
• set-to-null If a parent row is deleted, the
  foreign key in the related table(s) is empted.

11
Transform EER Diagrams into Relations

• Simple attributes
• single fields (phone or zip) map directly into
  the relation.
• Composite attributes
• Use only their simple, component attributes.
  Mailing address is broken down to street, city,
  state, and zip.
• Multivalued attribute
• Becomes a separate relation (junction table) with
  a foreign key taken from the superior entity.

12
Mapping a Regular Entity

• The ERD with squares (tables) and ovals (fields)
  is drawn in a row of connected blocks.
• Each block is one field.
• The primary key field is usually show in bold and
  underlined.

13
Mapping a Composite Attribute
14
Mapping a Multivalued Attribute
15
Mapping Weak Entities

• Mapping Weak Entities
• Becomes a separate relation with a foreign key
  taken from the superior entity
• Primary key composed of
• Partial identifier of weak entity
• Primary key of identifying relation (strong
  entity)

16
Mapping Weak Entities (cont.)
NOTE the domain constraint for the foreign key
should NOT allow null value if DEPENDENT is a
weak entity
17
Mapping Binary Relationships

• One-to-Many
• Primary key on one side becomes a foreign key on
  the many side.
• Many-to-Many
• Create a new relation with the primary keys of
  the two entities as its primary key.
• One-to-One
• Primary key on the mandatory side becomes a
  foreign key on the optional side.

18
1M Relationship
19
1M Relationship (cont.)
20
MN Relationship
I new junction table (associative entity) will be
necessary
21
MN Relationship (cont.)
New intersection relation
22
11 Relationship
23
11 Relationship (cont.)
24
Mapping Associative Entities

• Identifier Not Assigned
• Default primary key for the association relation
  is composed of the primary keys of the two
  entities (as in MN relationship)
• Identifier Assigned
• It is natural and familiar to end-users
• Default identifier may not be unique

25
Mapping Associative Entities (cont.)
26
Mapping Associative Entities (cont.)
27
Mapping Associative Entities (cont.)
28
Mapping Associative Entities (cont.)
29
Map Unary (1N) Relationships

• One-to-Many - Recursive foreign key in the same
  relation
• Many-to-Many - Two relations
• One for the entity type
• One for an associative relation in which the
  primary key has two attributes, both taken from
  the primary key of the entity

30
Map Unary (MN) Relationships
One item the company has in inventory is made up
of many other items the company has in inventory.
31
Mapping Ternary Relationships

• There is one relation for each entity and one for
  the associative entity.
• Associative entity has foreign keys to each
  entity in the relationship.

32
Mapping Ternary Relationships (cont.)
Remember that the primary key MUST be unique
33
Mapping Supertype/Subtype Relationships

• One relation for supertype and for each subtype
• Supertype attributes (including identifier and
  subtype discriminator) go into supertype relation
• Subtype attributes go into each subtype primary
  key of supertype relation also becomes primary
  key of subtype relation
• 11 relationship established between supertype
  and each subtype, with supertype as primary table

34
Mapping Supertype/Subtype Relationships (cont.)
35
Mapping Supertype/Subtype Relationships (cont.)
These are implemented as one-to-one relationships