CHAPTER 2 Exploring Relational Database Components

1. ระบบฐานข้อมูลขั้นสูง(Advanced Database Systems) CHAPTER2Exploring Relational DatabaseComponents Lecturer AJ. SuwanJanin Phone: 089-148-3993 E-mail: suwanjanin@gmail.com

2. Outline • 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

3. Introduction on Chapter 2 • 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)

4. Introduction on Chapter 2 (cont.) • 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.

5. Conceptual Database Design Components • Conceptual design for Northwind DBM. Entity, Attribute, Relationship, Business Rule, and Intersection Data are the basic components , make up a conceptual database design.

6. Entities • 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

7. Attributes • 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 suchasthe 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. • Attributemust describe or characterize of entity in some way (for example, size, shape, color, quantity, location).

8. Relationships • 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.”

9. Relationships (cont.) 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-to-many, 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.

10. One-to-One Relationships • 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.

11. One-to-Many Relationships • 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.

12. Many-to-Many Relationships • 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,anypart can be made of many other parts, and at the same time, any partcanbe a component of many other parts.

13. Business Rules 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

14. Logical/Physical Database Design Components 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.

15. Columns and Data Types • 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.

16. Constraints 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.

17. Why Focus on Relational?

18. Relationships

19. View (Query) 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)

20. Q&A??????? THANK YOU FOR YOUR KIND ATTENTION !!! Lecturer AJ. SuwanJanin