Today 1 Relational Database Lesson Objectives Review Explain

Today 1

Relational Database Lesson Objectives • Review: Explain the Web Based Client Server Applications and Architecture, Web Database, Relational Database. • Understand the tasks of a database • Define primary key, foreign key, and table relationships • Apply normalization rules to a database 2

Review Flowchart of a Sample Web-based, three-tiered Client/Server architecture Web Browser Web Server Data Server Transaction server Application Servers 3

-Review- Web Database WHY Web database? • Maintaining a database is easier than maintaining one by one web pages • Using database makes easy searching elements of database • A database makes easy to serve same data with different ways for example a catagory, definition, age, or any fields on the database 4

Review Relational Databases consist of computer files but applications can not update these files directly. Applications send command to a Database Management System (DBMS) and receive response. Oracle, SQL Server, Sybase, Informix, Access are some DBMS. Almost whole modern databases are compatible with relational models. Basic element of a database is a Table. The columns on the table represent fields and the rows represent records. Now, you will see internet based table related to money collection. 5

“uyeler” Table In each record first field contains same type of data: uyekodu. Second fields include name, third fields lastname. . . uyekodu can not be second field or fifth field of any record or any record can not contain “adres” in this table as you know! 6

the tasks of DBMS All relational DBMSs must provide the followings in order to access data: SELECT: provides record selection according to determined fields of the table. (Example: list the records from uye table where last names start with “B”. ) PROJECT: It displays a table display which includes only some of the fields. (Example: list only “adi” and “soyadi” fields of the uye table. ) JOIN: It combines two table and displays as if they were only one table. Record values of first table combined with record values of second table and connection results can displayed as a temporary table. 7

uyeler table: Join satanlar table: satilik_paralar (Query): 8

Designing a Database 1. Non-Official Database Design: A. Define main entities (objects) in Application; All applications includes some objects and base of the applications depends on properties and relations of these objects. Lecture Planning System; Instructors, students, lectures. . . Airline Ticketing System; Customers, flight crew, flights, planes, airlines. . . Football league; players, teams, game statistics, antrenuers, fields. . . Order Entry System; Customers, orders, customer invoices, sales items, stock items, supplier invoices, 9

Designing a Database 1. Non-Official Database Design: B. Create a Table for each Entity: on the paper, Excel, Word, Access, or SQL Server. . . Whatever you use is not important in this stage. In each table, you need to define primary key that uniquelly identifies each record. Since sometimes only one field is not enough to uniquelly identify a record, the combination of more than one fields can be used as primary key. The primary key field can not be null and for each record it must include different (unique- can not be duplicated) data. 10

Designing a Database 1. . Non-Official Database Design: A. Add entity properties (attributes) for each main entity; What do you want to know related to the main entity that you defined in your application? In a customer table. . . Customer_id, name, surname, city country, postcode, email, telephone number, fax number, etc. . . If you application includes some printed forms, they make easy to define necessary fields! 11

Designing a Database 1. . Non-Official Database Design: D. Create additional tables for repeated properties; It is possible to see more than one properties for each primary key. In money collectors example, you may add “komite” field to the “uye” table. So you may keep the records of “komite uyeligi” – - If some of the members have more than one commitee membership! Are you going to create “komite_1”, “komite_2”, and “komite_3” fields. - If some of the members have 4 commitees membership! - What about empty “komite_x” fields in other records? !. - How do you make select, join, project? Which commitee field will be used? What happens the data in the other commitee fields? komite Table: Komite_Kodu, uye_Kodu, . . 1 komite has 0 or more than 1 member, 1 member can be a member of 0 or more than 1 komite 12

Designing a Database 1. . Non-Official Database Design: E. Ensure that, each field is a related property (attribute) of primary key(s); In temporary komite_uyelik table: which of the following are not belong to the table? Komite Kodu (first field of primary key) Uye Kodu (second field of primary key) Katılma Tarihi Komite Adı The field of “komite adı” is a property of “komite”, not a property of “komite uyelik”. It belongs to a “komite” table which is primary key is only “komite kodu”. In order to find a “komite adı” you must not need to “komite kodu” and “uye kodu” together. If you want to find “komite adları” for a certain “komite uyesi”? According to the 13 “komite kodu” you may connect the “komite uyelik” and “komite” tables.

Designing a Database 1. . Non-Official Database Design: F. Review or Revise Table Relations; • It means the tables to be connected have some common fields to be used as Primary Key. • The connection fields can have different names (if they have same type of data). • If you have created code tables, it is useful to include code descriptions. 2. Official Database Design: Now, normalization is the way to design database!!! Let’s review your database design information via some 63 215 lecture slides. 14

1: M Relationship 15

Example 1: M Relationship 16

17

Example 1: M Relationship 18

Example M: N Relationship 19

Example M: N Relationship 20

Converting M: N Relationship to 1: M Relationships 21

(con’t. ) 22

Database Tables and Normalization • Table is basic building block in database design • The table’s structure is of great interest of us • How do you recognize poor table structure, and produce a good table? NORMALIZATION • Normalization is process for assigning attributes to entities – Reduces data redundancies – Helps eliminate data anomalies – Produces controlled redundancies to link tables • Normalization works through a series of stages called normal forms – 1 NF - First normal form – 2 NF - Second normal form – 3 NF - Third normal form 23

Need for Normalization 24

25

Observations The structure does not match the requirements in Relational Database Model. It does not handle data very well. Examples; 1. The PROJ_NUM is apparently intended to be a primary key, or at least a part of a primary key, but it contains nulls. 2. The table entries invite the data inconsistencies. (the JOB_CLASS value “Elect. Engineer” might be entered as “Elect. Eng. ” in some cases, “El. Eng. ” in others, and “EE” in still others). 3. The table displays data redundancies. These data redundancies yield the following anomalies. Update Anomalies. Modifying the JOB_CLASS for employee 105 requires many alterations. Insertion Anomalies. Just to complete a row definition, a new employee must be assigned to a project. If the employee is not yet assigned, the imaginary project must be created in order to complete the employee data entry. Deletion Anomalies. If employee 103 quits, deletions must be made for every entry in which EMP_NUM = 103. As such deletions are made, other vital data are lost, too. 26

Conversion to 1 NF • Repeating groups must be eliminated – Proper primary key developed • Uniquely identifies attribute values (rows) • Combination of PROJ_NUM and EMP_NUM – Dependencies can be identified • Desirable dependencies based on primary key • Less desirable dependencies – Partial » based on part of composite primary key – Transitive » one nonprime attribute depends on another nonprime attribute 27

Conversion to 1 NF 28

Dependency Diagram (1 NF) Dependencies can be written in the following format; PROJ_NUM, EMP_NUM PROJ_NAME, EMP_NAME, JOB_CLASS, CHG_HOUR, HOURS PROJ_NUM PROJ_NAME EMP_NUM EMP_NAME, JOB_CLASS, CHG_HOURS JOB_CLASS CHG_HOUR Table structure can be shown in the format; TABLE NAME (PRIMARY_KEY_ATTRIBUTE(S), DEPENDENT ATTRIBUTES) CHARGE (PROJ_NUM, EMP_NUM, PROJ_NAME, EMP_NAME, JOB_CLASS, CHG_HOUR, HOURS) 29

1 NF Summarized The table is in first normal form (1 NF) if: • All key attributes defined • No repeating groups in table. (Each row, column intersection can contain one and only one value, not a set of values). • All attributes dependent on primary key 30

Conversion to 2 NF • • • Start with 1 NF format: Write each key component on a separate line Write original key on last line Each component is new table Write dependent attributes after each key 31

2 NF Conversion Results 32

2 NF Summarized The table is in second normal form (2 NF) if: • In 1 NF • Includes no partial dependencies – No attribute dependent on a portion of primary key • Still possible to exhibit transitive dependency – Attributes may be functionally dependent on nonkey attributes NOTE: Because a partial dependency can exist only if a table’s primary key is composed of several attributes, a table whose primary key consists of only a single attribute must automatically be in 2 NF if it is in 1 NF. 33

Conversion to 3 NF • Create separate table(s) to eliminate transitive functional dependencies 34

Summary Normalization is a technique used to design tables in which data redundancies are minimized. Higher normal forms are better than lower normal forms, because higher normal forms yield relatively fewer data redundancies. A table is in 1 NF when all the key attributes are defined and when all remaining attributes are dependent on the primary key. A Table in 1 NF can still contain both partial and transitive dependencies. 35

Summary (Con’t. ) A table is in 2 NF when it is in 1 NF and contains no partial dependencies. A 1 NF table is automatically in 2 NF if its primary key is based on only a single attribute. A table in 2 NF may still contain transitive dependencies. A table is in 3 NF if it is in 2 NF and contains no transitive dependencies. 36

37

38

39