Database Systems
Lecture # 7
s
Last Week Review
•Data Modeling Phase (Database Development)
•ER Model (Entity, Attributes, Relationship)
•Represented in form of ERD
•Entity (Person, Place, Object, Concept, Event)
• Weak & Strong Entity
• Entity Type & Entity Instance
• Representation on ERD
•Attributes
1. Required Versus Optional Attributes
2. Simple vs. Composite Attributes
3. Single-Values vs. Multi-valued Attributes
4. Stored vs. Derived Attributes
5. Identifier Attributes
Attribute Types
1. Key Attribute –
•The attribute which uniquely identifies
each entity in the entity set is called key
attribute.
•For example, Roll_No will be unique for
each student.
•In ER diagram, key attribute is represented
by an oval with underlying lines.
Attribute Types
2. Composite Attribute –
An attribute composed of many other
attribute is called as composite attribute.
•For example, Address attribute of student
Entity type consists of Street, City, State,
and Country.
•In ER diagram, composite attribute is
represented by an oval comprising of ovals.
Attribute Types
3. Multivalued Attribute –
An attribute consisting more than one
value for a given entity.
For example, Phone_No (can be more
than one for a given student).
In ER diagram, multivalued attribute is
represented by double oval.
Attribute Types
4. Derived Attribute –
An attribute which can be derived from
other attributes of the entity type is
known as derived attribute.
•e.g.; Age (can be derived from DOB).
•In ER diagram, derived attribute is
represented by dashed oval.
Attribute Types of a
student
Attributes on ERD
Employee_Name Skill
Employee_ID
EMPLOYEE
Yea
rs_E
m ploy
ed
Date_Employed
Identifier Selection
• Chose an identifier that will not change its value
• The attribute is guaranteed to have a valid value
and not to be Null
• Avoid the use of identifiers or keys, whose
structure shows classification, location, and so on.
CASE (Identify Entities & Attributes)
The products of a company are grouped into several product lines.
The identifier for a product is Product ID, whereas the identifier for
a product line is Product Line ID. We identify the following
additional attributes for product: Product Description, Product
Finish, and Product Standard Price. Another attribute for product
line is Product Line Name. A product line may group any number of
products but each group must has at least one product. Each
product must belong to exactly one product line.
Customers submit orders for products. The identifier for an order is
Order ID, Order Date, Days Passed since order is placed. A customer
may submit any number of orders, but need not submit any orders.
Each order is submitted by exactly one customer. The identifier for a
customer is Customer ID. Other attributes include Customer Name
(First Name, Last Name), Customer Address (Street No, City,
Country), and Customer Postal Code.
Relationship
s
IN ERD
Relationships
• Relationships are the glue that holds together the
various components of ER Model.
• Relationship is an meaningful association between
one or more entity types that is of interest to the
organization.
Relationships
An association representing an interaction among
the instances of one or more entity types.
A relationship has a verb name
A name that describes the nature of the relationship
- examples:
STDENT takes COURSE,
MANAGER supervises EMPLOYEE,
ENGINEER is assigned to PROJECT,
ITEM is used in PRODUCT…
Relationships
EMPLOYEE
Is_assigned PARKING PLACE
CUSTOMER
Receives INVOICE
EMPLOYEE Completes COURSE
Relationships
EMPLOYEE Is_assigned PARKING PLACE
CUSTOMER Receives INVOICE
EMPLOYEE Completes COURSE
CASE (Identify Relationship)
The products of a company are grouped into several product lines.
The identifier for a product is Product ID, whereas the identifier for a
product line is Product Line ID. We identify the following additional
attributes for product: Product Description, Product Finish, and
Product Standard Price. Another attribute for product line is Product
Line Name. A product line may group any number of products but
must group at least one product. Each product must belong to exactly
one product line.
Customers submit orders for products. The identifier for an order is
Order ID, Order Date, Days Passed since order is placed. A customer
may submit any number of orders, but need not submit any orders.
Each order is submitted by exactly one customer. The identifier for a
customer is Customer ID. Other attributes include Customer Name
(First Name, Last Name), Customer Address (Street No, City, Country),
and Customer Postal Code.
