Advance Database Systems

Overview of Data Modeling

Contents

• Data Dictionary

• Relational Schema Diagram

Data Dictionary • A data dictionary is a collection of descriptions of the data objects or

items in a data model for the benefit of programmers and others who need to refer to them.

Relational Schema Diagram

• A relational schema diagram is the skeleton structure that represents the conceptual view(transformed ERD) of the entire database.

• It defines how the data is organized and how the relations among them are associated.

• It expresses about the constraints/checks that are to be applied on the data.

ERD Reading

Logical Data Modeling/Transformation into Relations (Tables)

Relation

• Rows or Record or Tuples or Instances of an ENTITY

• Columns or Fields or Labels or Attributes in ERD

• Keys (Primary and Foreign Keys)

• Constraints / Restrictions / Limitations/ Check

Rows or Record or Tuples or Instances of an ENTITY

• A record contains all the information about a single ‘member’ of a table.

• It is a collection of attributes values.

• Records are also known as tuples.

Relation (Table)• A relation is a named, two-dimensional table of data. A Table consists of rows

(tuples or records) and columns (attributes or fields)

Requirements for a table to qualify as a relation

• Table must have a Unique Name in same Database

• Primary Key must be assigned; each record must be uniquely identified

• There are no repeating groups: two columns do not store similar information in the same table

• Every attribute value must be atomic (no multivalued attributes are allowed)

• Every Record in row must store unique values (can’t have two rows with exactly the same values against all their fields/columns)

• Attributes (columns) in tables must have unique names

• The order of the columns can be irrelevant/un-ordered

• The order of the rows can be irrelevant/un-ordered

Key Fields• Keys are special fields that serve two main purposes:

• Primary keys are Unique Identifiers of the relation(table) with not null constraint/check/limitation. Examples include person CnicNumber. This is how we can guarantee that all rows(tuples/record) are unique and having some value against the attribute.

• Foreign keys are attribute in a relation (table) of a database that serves in a child relation (table) connecting parent relation (table) in the same database.

• Keys can be simple(a single field) or composite(more than one fields)

• Keys usually are used as Indexes to speed up the response to user queries

(Primary Key)

(Foreign Key)

(Combined, these are a

composite primary key)

Integrity Constraints/Limitations/Check/Restrictions

1) Domain Constraints:

• Allowable values for an Attribute (Data Type of a Label/Column/Attribute). See Table 5-1 on next slide.

2) Entity Integrity:

• No primary key attribute have null value. All primary key fields must contain Unique data.

3) Referential Integrity:• This rule states that any foreign key value(on the relation/table of the many

side) MUST match a primary key value in the relation/table of the one side in one_to_many type of relationship. (Foreign key may store a null value but not Primary key)

• For example: Update/Delete/Restrict Rules• Restrict: don’t allow update or delete of parent side records(P.K) if related

records that exists independent side/child table(F.K)• Cascade: automatically update or delete dependent side/child table

records(F.K) that relates with the parent side records(P.K)• Set-to-Null: the foreign key (F.K) in the dependent side set to null if

deleting from the parent side (P.K)

SQL table definitions

Referential

integrity

constraints are

implemented

with foreign key

to primary key

references

Well Structured Relation :

Insertion Anomaly/Irregularity/Inconsistency:• Suppose that we need to add a new employee to the table shown in

next slide. The primary key for this relation is the combination of Emp_ID and Course_Title. There fore, to insert a new record, the user must supply values for both Emp_ID and Course_Title(because primary key values can not be null or non existent). This is an anomaly. Because the user should not be able to enter only employee data without supplying course data.

Example of an Insertion, Deletion & Modification Anomaly/Irregularity/Inconsistency

EMPLOYEE_to_COURSE:

• An EMPLOYEE must studies at least one COURSE. A COURSE must be studied by one or more than one EMPLOYEE's(MANY_to_MANY type of Relationship).

EMPLOYEE_to_DEPARTMENT:

• An EMPLOYEE must do his/her job in at most one DEPARTMENT. In a DEPARTMENT at most one EMPLOYEE must do his/her job(ONE_to_ONE type of Relationship).

Transforming/Mapping ENTITIES into Relations (Tables)

• Mapping Regular Entities to Relations

1.Simple Attributes: E-R attributes map directly onto the relation

2.Composite Attributes: Use only their simple, component attributes

3.Multivalued Attribute: Becomes a separate relation with a foreign key taken from the superior entity.

Mapping a Regular Entity

(a) CUSTOMER entity type with

simple attributes

(b) CUSTOMER Relation

(b) CUSTOMER relation with address detail

(a) CUSTOMER entity type

with composite attribute

Removing Multivalued Attributes from Tables

a) Table with Repeating Groups:

EMPLOYEE 2 Relation:

Mapping an Entity with a Multivalued Attribute

• Multivalued attribute becomes a separate relation with foreign key

• One–to–Many relationship between original entity and new relation

Mapping Weak Entities into Relation (Tables)

• Becomes a separate relation with a foreign key taken from the superior entity

Primary Key composed of:

• Primary Key of identifying relation (Strong Entity)

Example of Mapping a Weak Entity

Strong and Weak Entity Type Symbol of Representation

Transforming/Mapping Unary Relationship into Relations (Tables)

• 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

• Mapping a unary 1:N relationship

(b) EMPLOYEE relation

with recursive foreign key

(a) EMPLOYEE entity

with unary relationship

Mapping a unary M:N relationship

(a) Bill-of-materialsrelationships (M:N):

(b) ITEM and

COMPONENT

relations

Transforming/Mapping Binary Relationship into Relations (Tables)

• One-to-Many:

• Primary key on the one side becomes a foreign key on the many side.

• Many-to-Many:

• Create a new relation named as Associative/Junction Table with the primary keys of the two entities as its primary key in Binary Degree of Relationship.

• One-to-One:

• Primary key on the Strong/Independent side becomes a foreign key on the Weak/Dependent side.

Transforming/Mapping Ternary Relationship into Relations (Tables)

• One relation for each entity and one for the Associative entity

• Associative/Junction entity has foreign keys to each entity in the relationship