Chapter 2 Relational Data Model-part1

RELATIONAL DATA MODEL PART 1 DFC2033 Database System

Learning outcome 1 ¨  Define database ¨  Describe component of database table. ¤  Record (Tuple) ¤  Attribute (Field) ¤  Domain ¤  Degree ¤  Cardinality ¨  Describe characteristic of relation scheme. ¤  Relation Name ¤  Attribute Name ¤  Domain

Learning outcomes 2 ¨  Describe the relation instance ¨  Identify relation keys: ¤  Primary Key ¤  Candidate Key ¤  Foreign Key ¨  Explain Relational Model Relationship ¤  One to Many ¤  Many to Many ¤  Self Referencing ¨  Create connections between tables. ¨  Apply Integrity rules to several tables in a given database.

Database 3 ¨  Definition ¤  Database is a shared, integrated computer structure that houses the collection of: n  End User Data : users’ data n  Metadata: description about data (description about table, attributes etc.) ¨  Data ¤  Raw facts about, unprocessed facts. ¤  e.g.: Aminah, 0102539937 ¨  Description ¤  Store metadata- data about data ¤  Know as System Catalog / data dictionary ¤  e.g.: Name : VARCHAR(10)

Database 4 ¨  Example of database encounter in daily life: ¤  Airline reservation ¤  Purchasing from supermarket ¤  Using local library

Relational Database Model 6 ¨  First proposed by Dr. E.F. Codd of IBM Research in 1970 ¨  Represent database as a collection of relation. ¨  Informally, each relation looks like a table of values. ¨  Represent database as a collections of relations. ¨  Relational database matches data by using common characteristics found within the data set. ¨  The resulting groups of data are organized and are much easier for people to understand.

Relational Database Model 7 ¨  Basic Structure ¤  Implemented through Relational DBMS (RDBMS) ¤  RDBMS manage the complexity of physical implementation. ¤  Users see database as a collection of tables in which data are stored. ¤  Tables are also known as Relation. ¤  Tables contains Attributes. ¤  Attributes are a set of characteristics of the table. ¤  Tables are related to each other through some shared attributes. ¤  The relationship type is shown in Relational Schema.

Component of database table 9 ¨  Entity ¨  Attribute/Column (Field) ¨  Domain ¨  Degree ¨  Record/Row (Tuple) ¨  Cardinality

Entity 10 ¨  Things or object in real world with independent existence. ¨  An entity may be object with: ¤  physical existence : person, car, house, employed, etc. ¤  conceptual existence : job, course, event.

Attribute 11 ¨  Attribute (Field) ¤  Each entity has certain characteristics known as attributes. ¤  Named column of relation. ¤  Example : an EMPLOYEE entity maybe describe by employee's name, age, address, salary and job. ¤  Used to hold information about the objects to be represented in database.

Simple & Composite Attributes 12 ¨  Simple Attributes ¤  Attribute that cannot be further divided ¤  Simple (Atomic) attributes. n  Age, City, Postal Code ¨  Composite Attributes ¤  Comprise of more than one attributes. n  Can be divided into smaller subparts, which represent more basic attributes with independent meaning. n  Address consist of streetNumber, postalCode, city

Single valued & Multivalued Attributes 13 ¨  Single Valued Attributes ¤  Can only have one value for the particular entity. ¤  Eg: A student can have only one IC number ¨  Multivalued Attributes ¤  Attributes that can have many values. ¤  Eg: A student can have many certificate such as PMR, SPM, MLVK, etc.

Stored & Derived Attributes 14 ¨  Stored Attributes ¤  The date_of_birth is the stored attribute. ¨  Derived attributes ¤  Can be computed from other attributes ¤  Example: age is derived from date_of_birth. ¤  Age is called derived attribute,

Null values 15 ¨  In some cases, particular entity may not have an applicable value for an attribute. ¨  Two cases: 1.  The attribute value is missing. n  E.g.: Height attribute is NULL. 2.  It is not known whether the attribute value exist. n  College degree – some people may have it and some not. ¨  Therefore, NULL value has to be created

Domain 16 ¨  Set of possible values for an attribute. ¨  Each simple attribute of entity is associated with value set (domain of values) ¨  It specify the sets value that may be assigned to that attribute for each individual entity. ¨  Example given an attribute named GENDER, the domains are MALE and FEMALE. ¨  Formally written as: GENDER = {MALE, FEMALE}

Degree 17 ¨  Degree ¤  The degree of relation is the number of attributes it contains. ¤  For example, Student has four attributes, or degree four.

Tuple 18 ¨  Record (Tuple) ¨  Row of a relation.

Cardinality 19 ¨  Cardinality ¤  The number of tuples(rows) its contains. ¤  If Branch relation have 5 number of tuples, the cardinality of Branch relation is 5. ¤  Change as the number of tuples is added or deleted.

Relational Scheme 22 ¨  Named relation defined by a set of attribute and domain name pairs. ¨  Common convention: ¤  RelationName (attribute_1, attribute_2,….., attribute_n) ¨  Example: ¤  Branch (branchNo, street, city, postcode)

Characteristics of Relation Scheme 23 ¨  What is Relation Schema? ¤  A named of a relation defined by a set of attributes and domain name pairs. ¨  Relation name ¤  Relation name is distinct from all other relation names in relational schema. ¤  Cannot have two Student relation in the database.

Characteristics of relation scheme 24 ¨  Attribute name ¤  Each attribute has a distinct name. ¤  Order of attributes has no significance. ¤  Order of tuples has no significance, theoretically. ¨  Domains ¤  Values of an attribute are all from the same domain.

Characteristics of relation scheme 25 Different order of tuple don’t have any significance

Relational Database schema 26 ¨  A sets of relation schema. ¨  The example shows the database schema for COMPANY = {EMPLOYEE, DEPARTMENT, DEPENDENT, PROJECT} Relation Schema DB Schema

Relational Database schema 27 ¨  The relation schema normally represented as follows: ¨  The primary key is underlined. EMPLOYEE { SSN, FNAME, LNAME, MINIT, BDATE, ADDRESS, SEX, SALARY, SUPERSSN,DNO } DEPARTMENT { DNUM, DNAME, MGRSSN, MGRSTTDATE }

Relation instance 28 ¨  A tuple at a specific moment of time ¨  Eg: Branch (BranchNo, Street, City, PostalCode ) ¨  The relation instance for branch is: ¤  (B005, 55 Jln Dobi, Johor Bahru, 80100) ¤  (B006, 55 Jalan Perai, Johor Bahru, 80000) ¨  The relation instance change when tuple is updated, deleted or inserted.

Group discussion 29 ¨  Create a relational database schema for Politeknik Balik Pulau.

Relation Keys 30 ¨  Refers to the important attribute in an entity. ¨  Determine the uniqueness of an row in given table. ¨  Identifiers for each rows. ¨  An attribute or more than one attributes can be declared as keys depending on situations. ¨  Types of keys: ¤  Primary Key ¤  Candidate Key ¤  Alternate Key ¤  Foreign key

Primary Key 31 ¨  Is an attributes that uniquely identify each rows. ¨  Each table must have primary key. ¨  Cannot be NULL value to maintain Entity Integrity. ¨  Primary key is the one that officially declared as the row identifier inside a specific table. ¨  Candidate keys for table Student: ¨  Eg: studentID for Table Students

Candidate key 32 ¨  A candidate key is a single field or the least combination of fields that uniquely identifies each record in the table. ¨  There can be more than one candidate keys in a relation. ¨  Criteria for the candidate keys: ¤  It must contain unique values ¤  It must not contain null values ¤  It contains the minimum number of fields to ensure uniqueness ¤  It must uniquely identify each record in the table

Candidate key 33 ¨  Candidate Keys: ¤  {StudentID} ¤  {firstName,lastName}

Candidate key 34 ¨  However, the instance of relation cannot be used to proved attributes or combination of attributes is a candidate key. ¨  What happen if another person has name James McCloud as the first and last name? ¨  Identifying the candidate keys require that we know “the real world” meaning of the attribute involved.

Alternate key ¨  An alternate key is any candidate key that is not primary key. ¨  Alternate keys are sometimes referred as secondary keys.

Foreign Key 35 ¨  Attribute is an attributes whose value match the primary key values in related tables. ¨  Creates a relationship between relations

Example 36 ¨  Branch (branchNo, street, city, postCode) ¨  Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo) ¨  FK à branchNo references Branch (branchNo)

Component in relational schema 37

Group discussion 38 DriverID dFName dLName D456 Jane Watt D666 Karen Black D957 Steven Smith D344 Tom Jones ClientID FName cEmail cAddress C3034 Anne Way 111 Storie Road C089 Mark Fields 120 Lady Jane C019 Anne Brown 13 Renfrew Road C039 Karen Ways 34 High Street DriverID ClientID pickupDate D456 C3034 2/1/10 D456 C089 2/1/10 D666 C3034 2/1/10 D344 C039 2/1/10 For each table, find: 1.  Two candidate keys 2.  Primary Key 3.  Foreign Key

Integrity rules 39 ¨  To have a good design, a database must have integrity rules. ¨  Constraint or restriction that apply to all instances of the database. ¨  Integrity rules consists of: ¤  Entity Integrity ¤  Referential Integrity

Entity integrity 40 ¨  Requirement ¤  All Primary Key entries are unique, and no part of a primary key may be NULL. ¨  Purpose ¤  Guarantee that each entity will have a unique identity and ensures that for each key value can properly refer primary key values. ¨  Examples: ¤  INVOICE, no invoice can have a duplicate number, nor can it be NULL. ¤  All invoices are UNIQUELY identified by their invoice number.

Referential integrity 41 ¨  Requirement ¤  A Foreign Key may have either a NULL entry, as long as it is not a part of its table’s primary key or an entry that matches the primary key value in a table to which is related. (Every non-null foreign key value must reference an existing primary key value) ¨  Purpose ¤  Makes it possible for an attribute NOT to have a corresponding value, but will be impossible to have an invalid entry. ¤  The enforcement of the referential integrity rules makes it impossible to delete a row in one table whose primary keys has mandatory matching foreign key values on another table. ¨  Examples: ¤  A customer might not (yet) have an assigned customer sales representative (CSR) number, but it will be impossible to have an invalid CSR number.

Example 42 ¤  Branch and Staff Relation. ¤  It is not possible to create staff record with branchNo B025, unless there is already record for branch B025 in Branch relation. ¤  However, we should be able to create new staff record with NULL branch number to allow the situation where a new member staff has joined the company but has not yet assigned to a particular Branch.

Relational Model Relationship 44 ¨  An association between entities. ¨  Entities that participate in the relationship are called participants. ¨  Specifies how many instances of an entity relate to one instance of another entity. ¨  Types: ¤  One to Many (1:M) Relationship ¤  Many to Many (M:N) Relationship ¤  Self Referencing Relationship

One to many (1:M) 45 ¨  Table A may have many linked records in Table B ¨  But each record in Table B may have only one corresponding record in Table A.

One to many (1:M) 46 ¨  Example: ¤  A database in company keep track the information about the employee and their dependent. Each employee can have one or more dependent and each dependent belongs to one employee only.

Many to many (m:N) 47 ¨  Table A may have many linked records in Table B ¨  Table B may have only many record corresponding record in Table A. ¨  M: N relationship is not a good choice in database. ¨  Normally when we have M:N relationship, we have to separate into 1:M relationship.

Many to many (m:N) 48 ¨  Example: ¤  A course can be taken by many students and the students can take as many courses as they want.

Self referencing 49 ¨  Relationship to itself. ¨  E.g. Employee reports to Employee

Group discussion 50 A company has several departments. Each department has a supervisor and at least one employee. Employees must be assigned to at least one department. Projects will be given to the employee and it will be done in a group. At least one employee is assigned to one project or more. The important data files are the names of the departments, projects, supervisors and employees, as well as the supervisor and employee number, department code and unique project number. ¨  Identify the entity. ¨  Identify the relationship.

Chapter 2 Relational Data Model-part1

Chapter 2 Relational Data Model-part1

More Related Content

What's hot

Viewers also liked

Similar to Chapter 2 Relational Data Model-part1

Recently uploaded

Chapter 2 Relational Data Model-part1