Transcript การเขียนโปรแกรมและการประ

ระบบฐานข้อมูลขัน้ สูง
(Advanced Database Systems)
Lecturer
AJ. Suwan Janin
Phone: 089-148-3993
E-mail: [email protected]

Conceptual Database Design Components
 Entities
 Attributes
 Relationships
▪ One-to-One Relationships
▪ One-to-Many Relationships
▪ Many-to-Many Relationships
 Business Rules



Logical/Physical Database Design
Components
Constraints
Views
• explore the conceptual, logical and physical components
to relational model.
• Conceptual database design is a modeling the data in a
technology-independent manner.
• Person performs conceptual database design , called a
data modeler.
• Logical database design is the process of translating, or
mapping, the conceptual design into a logical design that
fits the chosen database model (relational, object-oriented,
object-relational, and so on).
• A specialist who performs logical database design is called
a database designer or database administrator (DBA)
• Final design step is physical
database design, involves
mapping the logical design to
one or more physical .
• DBMS manage database and
computer system on the
database running.
• DBA performs physical
database design. In the sections that follow, we explore
the components of a conceptual
database design, then the components
of a logical and physical design.

Conceptual design for Northwind DBM.
Entity, Attribute,
Relationship, Business Rule,
and Intersection Data are
the basic components ,
make up a conceptual
database design.
• An entity is a person, place, thing, event, or concept about
data is collected or the real world things in which have
sufficient interest to capture and store data about them in a
database.
• An entity is represented as a rectangle on the diagram. Just
about anything that can be named with a noun can be an
entity.
• External entity is an entity with which our database exchanges
data such as credit accounts for customers purchase, credit
bureaus ==> ID ==> credit report, or all data about customer.
• Credit bureau not appear in conceptual database design
appear in data flow diagrams as a source or destination of data
in Chapter 7
• Attribute is a unit fact that characterizes or describes an
entity in someway as names inside the rectangle represents
the entity to they belong.
• The attribute appears at the top of the rectangle (above the
horizontal line) is the unique identifier for the entity suchas
the name suggests, provides a unique value for each instance
of the entity.
• For example, the Customer_ID attribute is the unique
identifier for the Customer entity, customer must have a
unique value.
• Attribute must describe or characterize of entity in some
way (for example, size, shape, color, quantity, location).
• Relationships are the associations among the entities.
Databases are storing related data, the relationships
become the glue that holds the database together.
• Relationships are shown on the conceptual design diagram
as lines connecting one or more entities.
• The maximum cardinality may be one (where the line has no
special symbol on its end) or many (where the line has a
crow’s foot on the end).
• The minimum cardinality may be zero, denoted with a circle
drawn on the line, or one, denoted with a short vertical line or
tick mark drawn across the relationship line.
• Many data modelers use two vertical lines to mean “one
and only one.”
For the relationship between Customer and Order, for
example, we must ask two questions: “Each customer can
have how many orders?” followed by “Each order can have
how many customers?” Relationships may thus be classified
into three types: one-to-one, one-to-many, and many-tomany, as discussed in the following sections. Some people
will say many-to-one is also a relationship type, but in
reality, it is only a one-to-many relationship looked at with a
reverse perspective. Relationship types are best learned by
example. Getting the relationships right is essential to a
successful design.
• One-to-one If we were to track which employees had
other employees
as spouses, we would expect each to be married to
either zero or one other
employee.
• One-to-many It is very common to track the employment
“food chain” of who reports to whom. In most organizations,
people have only one supervisor or manager. Therefore, we
normally expect to see each employee reporting to zero or
one other employee, and employees who are managers
or supervisors to have one or more direct reports.
• Many-to-many In manufacturing, a common relationship has
to do with parts that make up a finished product. If you think
about the CD-ROM drive in a personal computer, for example,
you can easily imagine that it is made of multiple parts, and
yet, it is only one part of your personal computer. So,any part
can be made of many other parts, and at the same time, any
partcan be a component of many other parts.
A business rule is a policy, procedure, or standard that an
organization has adopted. Business rules are very
important in database design because they dictate
controls that must be placed upon the data. In Figure 2-1
Tables
The primary unit of storage in the relational model is the
table, which is a two dimensional structure composed of
rows and columns. Each row represents one occurrence of
the entity that the table represents, and each column
represents one attribute for that entity.
• Restricting the data in the column to
characters that make sense for the data
type (for example, all numeric digits or
only valid calendar dates).
• Providing a set of behaviors useful to
the database user. For example, if you
subtract a number from another number,
you get a number as a result; but if you
subtract a date from another date, you
get a number representing the elapsed
days between the two dates as a result.
• Assisting the RDBMS in efficiently
storing the column data. For example,
numbers can often be stored in an
internal numeric format that saves space,
compared with merely storing the
numeric digits as a string of characters.
Primary Key Constraints
A primary key is a column or a set of columns that uniquely identifies
each row in a table. A unique identifier in the conceptual design is thus
implemented as a primary key in the logical design. The small icon that
looks like a door key to the left of the Order ID field name in Figure 2-6
indicates that this column has been defined as the primary key of the
Orders table
Referential Constraints
Each relationship between entities in the conceptual design becomes a
referential constraint in the logical design. A referential constraint
(sometimes called a referential integrity constraint) is a constraint that
enforces a relationship among tables in a relational database.
Views serve a number of useful
functions:
• Hiding columns that the user
does not need to see (or
should not be allowed to see)
• Hiding rows from tables that
a user does not need to see (or
should not be allowed to see)
• Hiding complex database
operations such as table joins
• Improving query
performance (in some
RDBMSs, such as
Microsoft SQL Server)
THANK YOU FOR
YOUR KIND ATTENTION !!!
Lecturer
AJ. Suwan Janin