Physical Database Design Objectives In this section you

Physical Database Design Objectives In this section, you will learn to: • • • ©NIIT Identify the following kinds of relations: • Base tables • Query results • Views Create tables Alter tables Remove tables Query tables and work on them Define views Perform DML operations on views Identify the different types of views Identify data integrity constraints 1

Physical Database Design Pre-assessment Questions 1. Functional dependencies represent _______ relationships. 2. What is the term used for the intentional introduction of redundancy in a table in order to improve performance? 3. Each value of an attribute A in relation R is associated with precisely one value of attribute B. What is this called? 4. A table is said to be in the _____ when each cell of the table contains precisely one value. 5. In a relation, every determinant is a candidate key. This relation is in which normal form? ©NIIT Physical Database Design Lesson 3 A / Slide 2 of 13

Physical Database Design Solutions Ans 1. Many-to-one Ans 2. Denormalization Ans 3. Functional dependency Ans 4. First normal form Ans 5. Boyce-Codd normal form ©NIIT Physical Database Design Lesson 3 A / Slide 3 of 13

Physical Database Design Language Support for the Relational Model • • • ©NIIT Most relational database systems support a query language named Structured Query Language (SQL). SQL is a combination of three subordinate languages: • Data Definition Language (DDL) • Data Manipulation Language (DML) • Data Control Language (DCL) The three important types of relations are: • Base tables • Query results • Views 4

Physical Database Design Base Tables • • ©NIIT A base table is a named table that physically exists in a database. The SQL statement to create a table is CREATE TABLE. You can alter an existing table by using the ALTER TABLE statement. You can remove a table by using the DROP TABLE statement. 5

Physical Database Design Query Results • • ©NIIT The results of queries made on a table are also tables. DML statements in SQL are used to query tables and work on them. SELECT is the most powerful DML statement of SQL. All relational operations can be performed by using the SELECT statement. You can remove the duplicate rows in queries by using the DISTINCT clause. SQL can impose an order on the result of a query through the ORDER BY clause. A query in which data is retrieved from more than one table is called a join query. There are two types of joins, equi-join and self join. The aggregate functions in SQL are COUNT, SUM, AVG, MAX, and MIN. 6

Physical Database Design Query Results (Contd. . ) • • • ©NIIT SQL provides a clause IS NULL (or IS NOT NULL) for finding a null value. A query within a query is called a subquery. The UNION operator of relational algebra is represented by the UNION clause in SQL. You can enter data in a table by using the INSERT statement. SQL provides the UPDATE statement for updating data. SQL provides the DELETE statement to delete a row. 7

Physical Database Design Views • • • ©NIIT A view is a named, derived, virtual table that does not exist physically. The tables that are the source of the data visible through the view are referred to as source tables. Views are defined by using the SQL statement CREATE VIEW. When a user makes a reference to a view, the DBMS finds the definition of the view stored in the database. The DBMS then translates the user’s request into an equivalent request against the source tables of the view. In this way, the DBMS maintains the illusion of the view. Views can be theoretically updatable or non-updatable. 8

Physical Database Design Types of Views • ©NIIT The different types of views are: • Column subset: A column subset view includes all rows but only some columns of the source table. • Row subset: A row subset view includes all columns but only some rows of the source table. • Row-column subset: A row-column subset view includes only some rows and some columns of the source table. • Grouped: A grouped view displays data that is the result of a grouped query. • Joined: A joined view is formed by specifying a two or three table query in the view definition. 9

Physical Database Design What are Views For? • • ©NIIT Views are an important part of a relational DBMS because of the following reasons: • Valid information • Restricted access • Simplified structure • Logical data independence The disadvantages of using views are: • Views affect DBMS performance. • A view may or may not be updatable. 10

Physical Database Design Data Integrity • • ©NIIT Data integrity refers to the correctness and completeness of the data in a database. Data integrity constraints restrict the data values that can be inserted into the database or updated in the database. Some typical data integrity constraints are: • Required data: A column should contain a non-null value. • Validity checking: The DBMS ensures that only data of the specified type is entered into the column. • Entity integrity: The DBMS checks for uniqueness of the primary key. • Referential integrity: The database must not contain any unmatched foreign key values. • Business rules: Many data integrity issues in the real world are concerned with the rules and procedures of an organization. A trigger is an action that takes place when an event occurs. 11

Physical Database Design Summary In this lesson, you learned that: • The three important types of relations are: • Base tables • Query results • Views • The SQL statement to create a table is CREATE TABLE. • You can alter an existing table by using the ALTER TABLE statement. • You can remove a table by using the DROP TABLE statement. • The SQL statements to query tables and work on them are: SELECT, INSERT, UPDATE, and DELETE. • A view is a named, derived, virtual table that does not exist physically. • Views are defined by using the SQL statement CREATE VIEW. • Views provide valid information, restricted and simplified access, simplified structure, and logical data independence. ©NIIT 12

Physical Database Design Summary (Contd. . ) • • • ©NIIT The different types of views are: • Column subset • Row-column subset • Grouped • Joined Data integrity refers to the correctness and completeness of the data in a database. Some typical data integrity constraints are: • Required data • Validity checking • Entity integrity • Referential integrity • Business rules 13