Lecture 2 database management system.pdf

Lecture 2 database in Nile University

1. Lecture2:
Modeling Data in the Organization
CSCI305
Database Systems
Course Instructors: / Dr. Amira Gaber

2. define each of the following key terms:, entity relationship model (E-R model), entity-relati
onship diagram (E-R diagram), entity, entity type, entity instance, strong entity type, weak
entity type, identifying relationship, attribute, composite attribute, simple attribute, multivalu
ed attribute, derived attribute, identifier, composite identifier, relationship type, relationship
instance, associative entity ,degree , unary relationship, binary relationship, ternary
relationship, cardinality constraint, minimum cardinality, maximum cardinality.
Write good names and definitions for entities.
Distinguish unary, binary, and ternary relationships and give a common example of each.
Model each of the following constructs in an E-R diagram: composite attribute, multivalue
d attribute, derived tribute, associative entity, identifying relationship, and minimum and
maximum cardinality constraints.
Draw an E-R diagram to represent common business situations.

3. analysis
design

4. conceptual data model
➢Conceptual Data Modelis an organized view of database concepts
and their relationships
➢a conceptual data model, a representation of the structure and
constraints of a database
➢conceptual data model is independent of software (such as a database
management system).
➢The Entity-relationship model (E-R model) is used to construct
a conceptual data model,
➢Entity-relationship diagram (E-R diagram, or ERD)
A graphical representation of an entity-relationship model

5. Sample E-R Diagram

6. E-R Model Constructs
Entity
•person, place, object,
event, concept
•Example : student,
course
Attribute
•property or
characteristic of an
entity type
•Example :student_name
, course ID
Relationship
•link between entities
•Example Student assign
course
Chen
notation
Crow foot
notation

7. Basic E-R Notation
Entity
symbols
Relationship
symbols
Attribute
symbols
A special entity
that is also a
relationship

8. Entity
An entity is a person, a place, an object, an event, or a concept in the
user environment about which the organization wishes to maintain data
Person:EMPLOYEE, STUDENT, PATIENT
Place:STORE, WAREHOUSE, STATE
Object:MACHINE, BUILDING, AUTOMOBILE
Event:SALE, REGISTRATION, RENEWAL
Concept: ACCOUNT, COURSE, WORK CENTER
To find entities,
simply look for nouns in your business analysis …

9. ENTITY TYPE VERSUS ENTITY INSTANCE
An entity type is a collection of entities that share common properties or
characteristics. Each entity type in an E-R model is given a name.
An entity instance is a single occurrence of an entity type.
entity type entity instance

10. What Should an Entity Be?
SHOULD BE:
–An object that will have many instances in the database
–An object that will be composed of multiple attributes
–An object that we are trying to model
SHOULD NOT BE:
–A userof the database system
–An outputof the database system (e.g. a report)

11. What Should an Entity Be?(cont.)
Inappropriate entities
System user System output
Appropriate entities
database needed for a college
sorority’s expense system. the
sorority treasurer manages
accounts, receives expense
reports, and records expense
transactions against each account

12. STRONG VERSUS WEAK ENTITY TYPES
A strong entity type is one that exists independently of other entity types. (S
ome data modeling software, in fact, use the term independent entity.)
Examples include STUDENT, EMPLOYEE, AUTOMOBILE, and COURSE.
Weak entity type is an entity type whose existence depends on some other
entity type.
Examples :
Employee and its dependents (Parents, Children, Spouse)
Dependent Employee

13. NAMING AND DEFINING ENTITY TYPES
special guidelines for naming entity types,
➢An entity type name is a singular noun (CUSTOMER, STUDENT)
➢An entity type name should be specific to the organization
▪CUSTOMER in one organization and CLIENTof another
▪a PURCHASE ORDER for orders placed with suppliers is distinct from
CUSTOMER ORDER for orders placed with us by our customers. Both of these
entity types cannot be named ORDER
➢An entity type name should be concise, using as few words as possible
➢Event entity types should be named for the result of the event, not the activity or
process of the event
➢The nameused for the same entity type should be the same on all E-R diagrams on
which the entity type appears

14. Attributes
➢An attributeis a descriptive property or characteristic of an entity.
Synonyms include element, property, and field
➢Elementarypiece of information about an entity
➢To find attributes, simply look for descriptive nouns that won’t be used as entities
and/or adjectives in your business analysis
➢Each entity will generally have several to many attributes
Examples:
–STUDENT: Student ID, Student Name, Home Address, Phone Number.
–AUTOMOBILE :Vehicle ID, Color, Weight, Horsepower
–EMPLOYEE :Employee ID, Employee Name, Employee Address, Skills

15. An Entity Relationship Diagram (ERD)(example)
Student
ID Name
Phone
number
Address
Automobile
ID color
Weight
Student
ID
Name
Address
phone
Automobile
ID
color
weight

16. Identifiers (Keys)
Identifier(Key) -An attribute (or combination of attributes
) that uniquely identifies individual instances of an entity
type
SimpleKey versus CompositeKey
Candidate Key –an attribute that could be a key…
satisfies the requirements for being a key

17. Characteristics of Identifiers
➢Will not change in value
➢Will not be null
➢No intelligent identifiers (e.g. containing locations or people that might
change)
➢Substitute new, simple keys for long, composite keys

18. Classification of Attributes
–Simpleversus CompositeAttribute
–Single-Valuedversus MultivaluedAttribute
–Storedversus DerivedAttributes
–IdentifierAttributes

19. Simple attribute
Simple attribute− Simple attributes are atomic values, which cannot be divided
further. For example, a student's phone number is an atomic value of 10 digits.
weight
Simple attribute

20. A composite attribute
An attribute broken
into component parts
Compositeattribute−Compositeattributesaremadeofmorethanonesimpleattribute.Forexample,astudent's
completenamemayhavefirst_nameandlast_name,studentaddress

21. Simple key attribute
The key is underlined

22. Composite key attribute
The key is composed
of two subparts

23. a multivalued attribute and derived attribute
Derived
from date employed and
current date
What’s wrong with this?
Multivalued:
an employee can have
more than one skill

24. attribute that is both multivalued and composite

25. Relationships
How entities depend on each other in terms of why the
entities depend on each other (the relationship) and what
that relationship is (the cardinality of the relationship).
➢Relationshipsare the associations between the entities
➢Cardinality:it defines the possible number of occurrences
in one entity which is associated with the number of occurre
nces in another
person project
Assigned
Relationship Types

26. Degree of Relationships
Degree of a Relationship is the number of entity typesthat participate in it
–UnaryRelationship :One entity related to another of the same entity
type
–BinaryRelationship: Entities of two different types related to each
other
–TernaryRelationship: Entities of three different types related
to each other

27. Cardinality of Relationships
One –to –One
–Each entity in the relationship will have exactly one related entity
One –to –Many
–An entity on one side of the relationship can have many related entities,
but an entity on the other side will have a maximum of one related entity
Many –to –Many
–Entities on both sides of the relationship can have many related entities
on the other side

28. Cardinality Constraints
Cardinality Constraints -the number of instances of one entity that can or m
ust be associated with each instance of another entity.
Minimum Cardinality
–If zero, then optional
–If one or more, then mandatory
Maximum Cardinality
–The maximum number

29. Unary relationships
manager
employee
Optional cardinalities with unary degree (one-to one)
wife
Husband

30. Examples of binary Relationships
and Their Cardinalities
customer order
Student Instructor
artist song
Student
courses
employee
Parking space

31. Example:Ternary relationships
Note: a relationship can have attributes of its own

32. Break
test your self

33. Entity Relationship Diagram Example:
•In a university, a Student enrolls in Courses. A student must be
assigned to at least one or more Courses.
•Each course may be assigned by one or more student
•Each course is taught by a single Professor.
•To maintain instruction quality, a Professor can deliver only one course
•Step 1) Entity Identification
•Step 2) Relationship Identification
•Step 3) Cardinality Identification
•Step 4) Identify Attributes(if there exist)

34. Entity Relationship Diagram Example:
Step 1) Entity Identification
We have three entities
•Student
•Course
•Professor
Student Course professor
Step 1) Entity Identification
In a university, a Student
enrolls in Courses.A student
must be assigned to at least
one or more Courses. Each
course is taught by a single
Professor. To maintain
instruction quality, a Professor
can deliver only one course

35. Entity Relationship Diagram Example:
Step 2) Relationship Identification
We have the following two relationships
•The student isassigneda course
•Professordeliversa course
Student Course professor
assigned delivers
In a university, a Student
enrolls in Courses. A student
must be assignedto at least
one or more Courses. Each
course is taught by a single
Professor. To maintain
instruction quality, a Professor
can deliver only one course

36. Entity Relationship Diagram Example:
Step 3) Cardinality Identification
•A student can be assignedmultiplecourses
•A Professor can deliver onlyonecourse
Student Course professor
assigned delivers
In a university, a Student enrolls in
Courses. A studentmust be assigned to
at least one or moreCourses. Each course
is taught by a single Professor. To
maintain instruction quality, a Professor
can deliver only one course

37. More on Relationships
Relationship Types vs. Relationship Instances
◦The relationship type is modeled as the diamond and lines between entity types…the
instance is between specific entity instances
Relationships can have attributes
◦These describe features pertaining to the association between the entities in the
relationship
Two entities can have more than one type of relationship between them (multiple
relationships)
Associative Entity = combination of relationship and entity
◦More on this later

38. Relationship Types vs. Relationship Instances
Relationship Types
Entity and Relationship
instances

39. Relationship with attributes
Here, the date completed attribute pertains specifically to the employee’s
completion of a course…it is an attribute of the relationship

40. Relationship with attributes(cont.)
Representing a bill-of -materials structure

41. Examples of multiple relationships
entities can be related to one another in more than one way

42. max cardinality constraint

43. Strong vs. Weak Entities, and
Identifying Relationships
Strong entities
–exist independentlyof other types of entities
–has its own unique identifier
–represented with single-linerectangle
Weak entity
–dependent on a strong entity…cannot exist on its own
–Does not have a unique identifier
–represented with double-linerectangle
Identifying relationship
–links strong entities to weak entities
–represented with double line diamond

44. Strong vs. Weak Entities, and
Identifying Relationships(cont.)
Strong entity Weak entityIdentifying relationship

45. Multivalued attribute vs. relationship.
Alternative approaches

46. Associative Entities
It’s an entity–it has attributes,AND it’s a relationship–it links entities together
How do you know whether to convert a relationship to an associative entity type?
1.All the relationships for the participating entity types are “many” relationships.
2.The resulting associative entity type has independent meaning to end users and,
preferably, can be identified with a single-attribute identifier.
3.The associative entity has one or more attributes in addition to the identifier.
4.The associative entity participates in one or more relationships independent of the
entities related in the associated relationship.
Ternary relationships are converted to associative entities

47. Associative Entities(cont.)
Associative entity involves a rectangle with a diamond inside.
Note that the many-to-many cardinality symbols face toward the
associative entity and not toward the other entities

48. Associative Entities(cont.)
This could just be a relationship
with attributes… it’s a judgment call

49. Associative Entities(cont.)
1)many vendor can supply many parts
to any number of warehouse
2) each part can be supplied by any
number of vendors to more than one
warehouse, but each part must be
supplied by at least one vendor to
a warehouse
3) each warehouse can by supplied with
any number of parts from more than
one vendor, but each warehouse
must be supplied with at least one part