LS569 Information Management Lecture 4

Slide 28: Left Outer Join

A left outer join yields all of the rows in the STUDENT table, including those that do not have a matching value in the PROFESSOR table. select * from student LEFT OUTER JOIN professor on student.prof_code=professor.prof_code;

Note: the composite ENROLL table

In addition to the linking attributes, the composite ENROLL table can also contain such relevant attributes as the grade earned in the course. In fact, a composite table can contain any number of attributes that the designer wants to track. Keep in mind that the composite entity, although implemented as an actual table, is conceptually a logical entity that was created as a means to an end: to eliminate the potential for multiple redundancies in the original M:N relationship.

Note: THETA JOIN

In formal terms, theta join is considered an extension of natural join. Theta join is denoted by adding a theta subscript after the JOIN symbol: ⋈ θ. Equijoin is then a special type of theta join.

Note: PRODUCT

PRODUCT is denoted by the multiplication symbol X. The PRODUCT of the CUSTOMER and AGENT relations would be written as follows: customer X agent A Cartesian product produces a set of sequences in which every member of one set is paired with every member of another set. In terms of relations, this means that every tuple in one relation is paired with every tuple in the second relation.

Slide 20: Figure 3.9 Book; Product

PRODUCT yields all possible pairs of rows from two tables—also known as the Cartesian product. Therefore, if one table has six rows and the other table has three rows, the PRODUCT yields a list composed of 6*3=18 rows. Select * from product1 Cross join store; First step in different type of joins we will see later.

Slide 15: Figure 3.4 Book; Select

SELECT, also known as RESTRICT, is referred to as a unary operator because it only uses one table as input. SELECT can be used to list all of the rows, or it can yield only rows that match a specified criterion.

Slide 3: The 1:M Relationship

The one-to-many (1:M) relationship is easily implemented in the relational model by putting the primary key of the "1" side in the table of the "many" side as a foreign key. Time Mark 6:13 in recording

Note: PROJECT

Formally, PROJECT is denoted by the Greek letter pi (π). Some sources use the uppercase letter, and other sources use the lovercase letter. Codd used the lowercase π in his original article on the relational model, and that is what we use here. Pi is followed by the list of attributes to be returned as subscripts, and then the relation listed in parentheses. For example, to PROJECT the CUS_FNAME and CUS_LNAME attributes in the CUSTOMER table, you would write the following: πcus_fname, cus_lname(customer) Since relational operators have the property of closure, that is, they accept relations as input and produce relations as output, it is possible to combine operators. For example, you can combine the two previous operators to find the customer first and last name of the customer with customer code 10010: πcus_fname, cus_lname (σcus_code=10010 (customer))

Slide 18: Figure 3.7 Book; Intersect

INTERSECT yields only the rows that appear in both tables. As with UNION, the tables must be union-compatible to yield valid results. 0~M(N) smaller (means it is a subset of another table) Select * from product1 Intersect Select * from product2

Slide 11: phpMyAdmin UI example

See image.

Slide 13: phpMyAdmin UI example defining tables (2)

See image.

Slide 5: Figure 3.22 Book; The Implemented 1:1 relationship between PROFESSOR and DEPARTMENT

The 1:1 "PROFESSOR chairs DEPARTMENT" relationship is implemented by having the EMP_NUM foreign key in the DEPARTMENT table. If you juse the employee number on the department code, How to determine the foreign key in a one to one relationship.

Slide 21: Figure 3.16 Book; Divide

The DIVIDE operation uses one 2-column table as the dividend and one single-column table as the divisor. The output of the DIVIDE operation is a single column that contains all values from the second column of the dividend (LOC) that are associated with every row in the divisor.

Note: The DIVIDE OPERATOR

The DIVIDE operator is denoted by the division symbol ÷. Given two relations, R and S, the DIVISION of them would be written: r ÷ s

Slide 30: Full Outer Join

The FULL OUTER JOIN return all records when there is a match in either left or right table records. select * from student LEFT OUTER JOIN professor on student.prof_code=professor.prof_code UNION select * from student RIGHT OUTER JOIN professor on student.prof_code=professor.prof_code;

Slide 2: Relationships within the Relational Database

•1:M relationship - Norm for relational databases •1:1 relationship - One entity can be related to only one other entity and vice versa •Many-to-many (M:N) relationship - Implemented by creating a new entity in 1:M relationships with the original entities •Composite entity (Bridge or associative entity): Helps avoid problems inherent to M:N relationships, includes the primary keys of tables to be linked A many-to-many (M:N) relationship is not supported directly in the relational environment.

Summary: Each table row must have a primary key.

Each table row must have a primary key. The primary key is an attribute or combination of attributes that uniquely identifies all remaining attributes found in any given row. Because a primary key must be unique, no null values are allowed if entity integrity is to be maintained.

Note: The one-to-many (1:M) relationship

The one-to-many (1:M) relationship is easily implemented in the relational model by putting the primary key of the "1" side in the table of the "many" side as a foreign key.

Slide 23: Outer Joins

•Outer join: Matched pairs are retained and unmatched values in the other table are left null •Left outer join: Yields all of the rows in the first table, including those that do not have a matching value in the second table •Right outer join: Yields all of the rows in the second table, including those that do not have matching values in the first table •Full outer join: combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause The labels 'left' and 'right' refer to the order in which the tables are listed in the SQL command.

Slide 31: Key Points for Quiz #2

1.I will give a relationship and you will need to state the business rule. (question will look lide slide 4 - relationship diagram - write down the business rules that govern this relationship: 1.Pay attention ot the words you use - especially the words indicating the min and max cardinality. 2.Min Max are one 3.One professor chairs one and only one department. 4.Business rule - one professor may chair one department (because of the difference in min cardinality) 5.Indicate relationship - One department 2.Advantages - of the relational model - straight forward - use last weeks slide. 3.No need to create queries. Question will look like - what does a where clause mean in a query. 4.If you are unable to view the images for questions 4 and 5, please refer to figures 3.21 and 3.23 in chapter 3. Please let me know if you have any questions about quiz 2.

Slide 25: Step 1

A natural join is the result of a three-stage process. First preform a product to return all possible pairs of two tables.

Summary: A relational database performs much of the data manipulation work behind the scenes.

A relational database performs much of the data manipulation work behind the scenes. For example, when you create a database, the RDBMS automatically produces a structure to house a data dictionary for your database. Each time you create a new table within the database, the RDBMS updates the data dictionary, thereby providing the database documentation.

Slide 29: Right Outer Join

A right outer join yields all of the rows in the PROFESSOR table, including those that do not have matching values in the STUDENT table. select * from student RIGHT OUTER JOIN professor on student.prof_code=professor.prof_code;

Note: The word relation

Also known as a dataset in Microsoft Access, is based on the mathematical set theiry from which Codd derived his model. Because the relational model uses attribute values to establish relationships among tables, many databae users incorrectly assume that the term relation refers to such relationships. Many then incorrectly conclude that only the relational model permits the use of relationships.

Summary: Although tables are independent, they can be linked by common attributes.

Although tables are independent, they can be linked by common attributes. Thus, the primary key of one table can appear as the foreign key in another table to which it is linked. Referential integrity dictates that the foreign key must contain values that match the primary key in the related table, or must contain nulls

Note: DIFFERENCE

DIFFERENCE is denoted by the minus symbol -. If the relations SUPPLIER and VENDOR are union-compatible, then a DIFFERENCE of SUPPLIER minus VENDOR would be written as follows: supplier - vendor Assuming the SUPPLIER and VENDOR tables are not union-compatible, producing a list of any supplier names that do not appear as vendor names, then you can use a DIFFERENCE operator. πsupplier_name (supplier) - π vendor_name (vendor)

Slide 19: Figure 3.8 Book; Difference

DIFFERENCE yields all rows in one table that are not found in the other table; that is, it subtracts one table from the other. As with UNION, the tables must be union-compatible to yield valid results. note that subtracting the first table from the second table is not the same as subtracting the second table from the first table. 0~M(N) in the case above - you do not have any shared attributes in the tables. The minimum number will be zero. Select * from product1 Where the P-code no in (select p_code from product2); Select * from product2 Where p_code not in (select p_code from product1);

Note: SELECT

Formally, SELECT is denoted by the lowercase Greek letter sigma (σ). Sigma is followed by the condition to be evaluated (called a predicate) as a subscript, and then the relation is listed in parentheses. For example, to SELECT all of the rows in the CUSTOMER table that have the value '10010' in the CUS_CODE attribute, you would write the following: σ cus_code=10010(customer)

Note: INTERSECT

INTERSECT is denoted by the symbol Ո. If the relations SUPPLIER and VENDOR are union-compatible, then an INTERSECT between them would be denoted as follows: supplier Ո vendor Just as with the UNION operator, it is unusual to find two relations that are union-compatible in a database so PROJECT operators are applied to relations to produce results that can be manipulated with an INTERSECT operator. For example, again assume the SUPPLIER and VENDOR tables are not union compatible. If you wish to produce a listing of any vendor and supplier names that are the same in both tables, then you can PROJECT the names from each table and then perform an INTERSECT with them. πsupplier_name (supplier) Ո π vendor_name (vendor)

Slide 12: phpMyAdmin UI example defining tables

If no value was specified for the AUTO_INCREMENT column, MySQL will assign sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled. When you select a column with type ZEROFILL it pads the displayed value of the field with zeros up to the display width specified in the column definition. Values longer than the display width are not truncated. the CURRENT_TIMESTAMP() function returns the current system timestamp of the server on which the SQL Server database instance is installed. It is returned as a datetime value without the time zone offset.

Slide 7: Figure 3.25 Book; Converting the M:N relationship into two 1:M relationships

In this example, the ENROLL table's primary key is the combination of its foreign keys CLASS_CODE and STU_NUM. (However, the designer could have decided to create a single-attribute new primary key such as ENROLL_LINE, using a different line value to identify each ENROLL table row uniquely. ) Because the ENROLL table links two tables, STUDENT and CLASS, it is also called a linking table. In other words, a linking table is the implementation of a composite entity. @35m mark - working with SQLWorkbench. How to implement a many to many relationship in a relational database. End @43m mark.

Note: A null is a no value at all.

It does not mean a zero or a space. A null is created when you press the Enter key or the Tab key to move to the next entry without making an entry of any kind. Pressing the Spacebar creates a blank (or a space).

Note: Natural join is normally just referred to as JOIN in formal treatments.

JOIN is denoted by the symbol ⋈. The JOIN of the CUSTOMER and AGENT relations would be written as follows: customer ⋈ agent Notice that the JOIN of two relations returns all of the attributes of both relations, except only one copy of the common attribute is returned. Formally, this is described as a UNION of the relvar headings. Therefore, the JOIN of the relations (c ⋈ a) includes the UNION of the relvars (C ⋃ A). Also note that, as described above, JOIN is not a fundamental relational algebra operator. It can be derived from other operators as follows: πcus_code, cus_lname, cus_fname, cus_initial,cus_renew_date, agent_code, agent_areacode, agent_phone, agent_lname, agent_ytd_sls (σcustomer.agent_code = agent.agent_code (customer X agent))

Summary: Keys are central to the use of relational tables.

Keys are central to the use of relational tables. Keys define functional dependencies; that is, other attributes are dependent on the key and can therefore be found if the key value is known. A key can be classified as a superkey, a candidate key, a primary key, a secondary key, or a foreign key.

Slide 27: Step 3 (Natural Join)

Last, yield a single copy of each attribute, thereby eliminating duplicate columns. select * from student NATURAL JOIN professor;

Summary: Once you know the basics of relational databases, you can concentrate on design.

Once you know the basics of relational databases, you can concentrate on design. Good design begins by identifying appropriate entities and their attributes and then the relationships among the entities. Those relationships (1:1, 1:M, and M:N) can be represented using ERDs. The use of ERDs allows you to create and evaluate simple logical design. The 1:M relationship is most easily incorporated in a good design; just make sure that the primary key of the "1" is included in the table of the "many."

Note: OUTER JOIN

Outer join is also an extension of JOIN. Outer joins are the application of JOIN, DIFFERENCE, UNION, and PRODUCT. A JOIN returns the matched tuples, DIFFERENCE finds the tuples in one table that have values in the common attribute that do not appear in the common attribute of the other relation, these unmatched tuples are combined with NULL values through a PRODUCT, and then a UNION combines these results into a single relation. Clearly, a defined outer join is a great simplification! Left and right outer joins are denoted by the symbols ⟕ (LEFT) and ⟖, (RIGHT) respectively.

Slide 16: Figure 3.5 Book; Project

PROJECT yields all values for selected attributes. It is also a unary operator, accepting only one table as input. PROJECT will return only the attributes requested, in the order in which they are requested.

Slide 26: Step 2 (Equijoin)

Second, yield only the rows for which the PROF_CODE values are equal. The result of an equijoin looks just like the outcome of step 2. select * from student JOIN professor on student.prof_code=professor.prof_code;

Key Term: associative entity

See composite entity. composite entity—An entity designed to transform an M:N relationship into two 1:M relationships. The composite entity's primary key comprises at least the primary keys of the entities that it connects. Also known as a bridge entity. See also linking table.

Slide 24: Question What is the result of natural join, equijoin, left outer join, right outer join, full outer join? See Book JOIN starting on page 84 Ch. 3

See image.

Summary: Tables are the basic building blocks of a relational database.

Tables are the basic building blocks of a relational database. A grouping of related entities, known as an entity set, is stored in a table. Conceptually speaking, the relational table is composed of intersecting rows (tuples) and columns. Each row represents a single entity, and each column represents the characteristics (attributes) of the entities.

Slide 6: Figure 3.26 Book; Changing the M:N Relationship to Two 1:M Relationships

The composite entity named ENROLL represents the linking table between STUDENT and CLASS. One student can have many classes = one class must have many students. Business Rule. Create a attribute primary key to break up a many to many relationship. One to many in Student to Enroll . Many in Enroll to one class. All many to many relationships should be displayed this way.. Table view MYSQLworkbench - much look like lower - or else you can not implement it. First

Note: Data Dictionary

The data dictionary in Table 3.6 is an example of the human view of the entities, attributes, and relationships. The purpose of this data dictionary is to ensure that all members of database design and implementation teams use the same table and attribute names and characteristics. The DBMS's internally stored data dictionary contains additional information about relationship types, entity and referential integrity checks and enforcement, and index types and components. This additional information is generated during the database implementation stage.

Note: Relational Completeness

The degree of relational completeness can be defined by the extent to which relational algebra is supported. To be considered minimally relational, the DBMS must support the key relational operators SELECT, PROJECT, and JOIN.

Note: Test of Redundancy

The real test of redundancy is not how many copies of a given attribute are stored, but whether the elimination of an attribute will eliminate information. Therefore, if you delete an attribute and the original information can still be generated through relational algebra, the inclusion of that attribute would be redundant. Given that view of redundancy, proper foreign keys are clearly not redundant in spite of their multiple occurrences in a table. However, even when you use this less restrictive view of redundancy, keep in mind that controlled redundancies are often designed as part of the system to ensure transaction speed and/or information requirements. Exclusive reliance on relational algebra to produce required information may lead to elegant designs that fail the test of practicality.

Ch 3 - History

The relational model, introduced by E.F. Codd in 1970, is based on predicate logic and set theory. Predicate logic, used extensively in mathematics, provides a framework in which an assertion (statement of fact) can be verified as either true or false. For example, suppose that a student with a student ID of 12345678 is named Melisaa Sanduski. Thies assertion can easily be demonstrated to be true or false. Set theory is a mathematical science that deals with sets, or groups of things, and is used as the basis for data manipulation in the relational model. For example, assume that set A contains three numbers: 16, 24, and 77. This set is presented as A(16,24,77). Futher more, set B contains four numbers: 44, 77, 90, and 11, and so is represented as B(44, 77, 90, 11). Given this information, you can conclude that the intersection of A and B yields a result with a single number, 77. This result can be expressed as A Ո B = 77. In other words, A and B share a common value, 77. Based on these concepts, the relational model has three well-defined components: 1. A logical data structure represented by relations (See Sections 3.1, 3.2, and 3.5) 2. A set of integrity rules to enforce that the data are and remain consistent over time (see Sections 3.3, 3.6, 3.7, and 3.8) 3. A set of operations that defines how data are manipulated (see Section 3.4)

Note: UNION

UNION is denoted by the symbol ⋃. If the relations SUPPLIER and VENDOR are union-compatible, then a UNION between them would be denoted as follows: supplier ⋃ vendor It is rather unusual to find two relations that are union-compatible in a database. Typically, PROJECT operators are applied to relations to produce results that are union compatible. For example, assume the SUPPLIER and VENDOR tables are not union compatible. If you wish to produce a listing of all vendor and supplier names, then you can PROJECT the names from each table and then perform a UNION with them. πsupplier_name (supplier) ⋃ πvendor_name(vendor)

Note: Relational database terminology is very precise.

Unfortunately, file system terminology sometimes creeps into the database environment. This, rows are sometimes referred to as records, and columns are sometimes labeled as fields. Occasionally, tables are labeled files. Technically speaking, this substitution of terms is not always appropriate; the database table is a logical concept rather than a physical concept, and the terms file, record, and field describe physical concepts. Nevertheless, as long as you recognize that the table is actually a logical concept rather than a physical construct, you may think of table rows as records and of table columns as fields. In fact, many database software vendors still use this familiar file system terminology.

Slide 10: Creating database/tables on Server

Window: no password Mac: root Open wamp - Localhost Click PHP myadmin Login with password Click on personal database Click - you can see tables you created. Type new Give it a name: Assignment 2 Click Create - To delete - select and click drop. @min69 Creating a Database Table on the server. What is Auto Increment in SQL? The auto increment in SQL is a feature that is applied to a field so that it can automatically generate and provide a unique value to every record that you enter into an SQL table. Click on preview SQL - generates a query -

Slide 8: Data Dictionary

•Data dictionary: Description of all tables in the database created by the user and designer •"the database designer's database" : it records the design decisions about tables and their structures. The data dictionary contains at least all of the attribute names and characteristics for each table in the system. In short, the data dictionary contains metadata-data about data. •See page #19 in Chapter 3. • •Homonyms and synonyms must be avoided to lessen confusion •Homonym: Same name is used to label different attributes •Synonym: Different names are used to describe the same attribute List of descriptions of all tables in the database created by the user and designer.

Slide 22: Inner Joins

•Inner join: Only returns matched records from the tables that are being joined •Natural join: Links tables by selecting only the rows with common values in their common attributes/columns •The associated tables have one or more pairs of identically named columns. •The columns must be the same data type. •ON clause is not applicable in a NATURAL JOIN. • •Equi join: Links tables on the basis of an equality condition that compares specified columns of each table •A EQUI JOIN is preformed by using JOIN keyword followed by ON keyword and then specifying names of the columns along with their associated tables to check equality. The final outcome of a natural join yields a table that does not include unmatched pairs and provides only the copies of the matches. The outcome of the equijoin does not eliminate duplicate columns.

Slide 14: Relational Algebra

•Theoretical way of manipulating table contents using relational operators The use of relational algebra operators on existing relations (tables)can produce new relations. the following slides introduces SELECT (or RESTRICT), PROJECT, UNION, INTERSECT, DIFFERENCE, PRODUCT, JOIN, and DIVIDE operators.

Slide 17: Figure 3.6 Book; Union

•Union-compatible •In order for two relations to be union-compatible, both must have the same number of attributes (columns) and corresponding attributes (columns) must have the same domain. •The Product, Join, and Divide (you will see later) operators can be applied to a pair of tables that are not union-compatible. UNION combines all rows from two tables, excluding duplicate rows. To be used in the UNION, the tables must have the same attribute characteristics; in other words, the columns and domains must be compatible. M(N) larger ~ M+N Select * from product1 Union Select * from product2; Run this query to see the result.

Slide 9: Data Dictionary Example (refer to for PROJECT #2)

Data Dictionary - example you will need to refer to for project 2. Pk/Fk - primary key - foreign key

Slide 1: The Relational Database Model (2)

N/A

Slide 4: The 1:1 Relationship

One department chair—a professor—can chair only one department, and one department can have only one department chair. BUSINESS RULE

Summary: The relational model supports several relational algebra functions, including SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, and DIVIDE.

The relational model supports several relational algebra functions, including SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, and DIVIDE. Understanding the basic mathematical forms of these functions gives a broader understanding of the data manipulation options.