CIS 3465 Professor Kirs Slide Number 1 RDBMS

CIS 3465: Professor Kirs Slide Number: 1 RDBMS Basics Chapter 2: The Relational Database Model (With Considerable Modifications)

CIS 3465: Professor Kirs Slide Number: 2 RDBMS Basics We already know that a Relational Data. Base Management System (RDBMS) is: • A DBMS Approach which manages data (logically) as a collection of tables where data, and data relationships, are represented by common values in related tables • A Table (file) consists of records, which consist of attributes (fields), which relies on the storage of various data types

CIS 3465: Professor Kirs Slide Number: 3 RDBMS Basics Consider some information the University maintains: Name Address SSN Major Tuition Paid Courses Taken Tuition Owed Grades Received Grants/Scholarships HOW is this information stored? You are an entity with attributes which vary. Within the University, different areas have different interests in you (i. e. , the Registrar, the Bursar, etc. ). Nonetheless, you are still part of the University as a whole. HOW does this relate to a database?

CIS 3465: Professor Kirs Slide Number: 4 RDBMS Basics Database Components: You are an entity with attributes which vary Record Fields can contain characters, numbers, symbols, etc. Within the University, Files different areas, have different interests in you (i. e, . The Registrar, Bursar, etc. ) Nonetheless, you are still part of the University Database

CIS 3465: Professor Kirs Slide Number: 5 RDBMS Basics Database Components: A Database consists of Files, which contain • • • • • • • • • • • • • • • • • • • • • • • • • • • Records, which contain Hernandez, Juan 123456789 72 2. 42 Jones, Mary 234567890 102 3. 87 Fields, which may consist of a variety of data types Notice that there should always be a Key (Unique) Field

CIS 3465: Professor Kirs Slide Number: 6 RDBMS Basics RDBMS Restrictions/Conventions: Each Relation MUST have a unique name Table Student. ID Name Address Major 123456789 Saenz, Lupe 123 Mesa Finance 234567890 Chung, Mei 37 5 th St. INFOSYS 345678901 Adams, John 54 B Hague Accounting 456789012 Elam, Mary 123 -22 E St. Accounting • • • • • • Table Balance NOT Allowed Table Student Owed Department SID • • • Depart 103456678 1, 502. 36 Marketing 987654321 • • • Finance 123456789 COBA 219 Finance 876543210 • • • INFOSYS 456789012 COBA 232 Accounting 765432109 • • • Accounting • • • • • • • • •

CIS 3465: Professor Kirs Slide Number: 7 RDBMS Basics RDBMS Restrictions /Conventions : Each Relation MUST have a unique name All Columns (Tuples) MUST have Unique names Table Student. ID Name Address Major 123456789 Saenz, Lupe 123 Mesa Arlington Finance 234567890 Chung, Mei 37 5 th St. New York INFOSYS 345678901 Adams, John 54 B Hague Dallas Accounting 456789012 Elam, Mary 123 -22 E St. Ft. Worth INFOSYS One of the names MUST be changed * NOTE: The same field names CAN be used in different Relations

CIS 3465: Professor Kirs Slide Number: 8 RDBMS Basics RDBMS Restrictions /Conventions : Each Relation MUST have a unique name All Columns (Tuples) MUST have Unique names All Column Elements MUST be of the same data type Table Student. ID Name Address Major 123456789 Saenz, Lupe 123 Mesa 24. 34 234567890 Chung, Mei 37 5 th St. INFOSYS 345678901 Adams, John 54 B Hague Accounting 456789012 Elam, Mary 123 -22 E St. INFOSYS Unless this is stored as Character String “ 24. 34” (and NOT as the real number 24. 34), it MUST be changed

CIS 3465: Professor Kirs Slide Number: 9 RDBMS Basics RDBMS Restrictions /Conventions : Each Relation MUST have a unique name All Columns (Tuples) MUST have Unique names All Column Elements MUST be of the same data type • Notice that this means each record requires the SAME number of Bytes of Storage Table Student. ID Name Address Major 123456789 Saenz, Lupe 123 Mesa Finance A Character String Requiring 9 Bytes of Storage A Character String Requiring 20 Bytes of Storage A Character String Requiring 25 Bytes of Storage A Character String Requiring 10 Bytes of Storage A Total 64 Bytes of Storage

CIS 3465: Professor Kirs Slide Number: 10 RDBMS Basics RDBMS Restrictions /Conventions : Each Relation MUST have a unique name All Columns (Tuples) MUST have Unique names All Column Elements MUST be of the same data type The order of Rows is NOT important Student. ID Name Address Major 123456789 Saenz, Lupe 123 Mesa Finance 234567890 Chung, Mei 37 5 th St. INFOSYS 345678901 Adams, John 54 B Hague Accounting Student. ID Name Address Major 123456789 Saenz, Lupe 123 Mesa Finace 345678901 Adams, John 54 B Hague Accounting 234567890 Chung, Mei 37 5 th St. INFOSYS Is the Same as

CIS 3465: Professor Kirs Slide Number: 11 RDBMS Basics RDBMS Restrictions /Conventions : Each Relation MUST have a unique name All Columns (Tuples) MUST have Unique names All Column Elements MUST be of the same data type The order of Rows is NOT important The Number of Bytes/Record Must be the same Student. ID Name Address Major 123456789 Saenz, Lupe 123 Mesa Finance 234567890 Chung, Mei 37 5 th St. INFOSYS 345678901 Adams, John 54 B Hague Accounting Each contains 64 Bytes

CIS 3465: Professor Kirs Slide Number: 12 RDBMS Basics RDBMS Keys: Purpose Define entity relationships Determination Knowing the value of a key field means you also know (Determine) the values of the other fields E. g. , knowing Student. ID means you know Student. Name, Student. Address, etc. Student. ID Student. Name, Student. Address (Student. ID Determines Student. Name and Student. Address)

CIS 3465: Professor Kirs Slide Number: 13 RDBMS Basics RDBMS Keys: Purpose Define entity relationships Determination Functional Dependence An attribute is functionally dependent on another if can be determined by that attribute Student. ID Student. Address (Student. ID Determines Student Address) NOTE: Student. Address Student. ID (Two Students MAY live at the same address)

CIS 3465: Professor Kirs Slide Number: 14 RDBMS Basics RDBMS Keys: Each Relation MUST have a unique identifier or PRIMARY KEY Table Student. ID Name Address Major 123456789 Saenz, Lupe 123 Mesa Finance 234567890 Chung, Mei 37 5 th St. INFOSYS 345678901 Adams, John 54 B Hague Accounting 456789012 Elam, Mary 123 -22 E St. INFOSYS • • • • • • No two students can have the same Student. ID

CIS 3465: Professor Kirs Slide Number: 15 RDBMS Basics RDBMS Keys: Each Relation MUST have a unique identifier or PRIMARY KEY A COMPOSITE KEY is a combination of keys (Multikey attributes) used to produce uniqueness Student. Name, Student. Address Student. Major (Student. Name AND Student. Address Determines Student. Major) NOTE: If Student Major is functionally dependent upon Student. Name AND Student. Address , BUT not on either Student. Name or Student. Name it is FULLY FUNCTIONAL DEPENDENT on the Concatenated key (Attributes are fully functionally dependent on PRIMARY KEYS)

CIS 3465: Professor Kirs Slide Number: 16 RDBMS Basics RDBMS Keys: Each Relation MUST have a unique identifier or PRIMARY KEY A is COMPOSITE KEY is a combination of keys (Multi-key attributes) used to produce uniqueness A is SUPER KEY is either a PRIMARY or COMPOSITE KEY that uniquely identifies an entity Student. ID Student. Address AND Student. Name, Student. Address Student. Major Are BOTH Superkeys

CIS 3465: Professor Kirs Slide Number: 17 RDBMS Basics RDBMS Keys: Each Relation MUST have a unique identifier or PRIMARY KEY A is COMPOSITE KEY is a combination of keys (Multi-key attributes) used to produce uniqueness A is SUPER KEY is either a PRIMARY or COMPOSITE KEY that uniquely identifies an entity A is CANDIDATE KEY is any key or group of keys that could become a SUPER KEY • Student. ID is a candidate key • Student. Name, Student. Address is a candidate key • Student. ID, Student. Address is NOT a candidate key (Student. ID by itself is a CANDIDATE KEY)

CIS 3465: Professor Kirs Slide Number: 18 RDBMS Basics RDBMS Keys: Each Relation MUST have a unique identifier or PRIMARY KEY A is COMPOSITE KEY is a combination of keys (Multi-key attributes) used to produce uniqueness A is SUPER KEY is either a PRIMARY or COMPOSITE KEY that uniquely identifies an entity A is CANDIDATE KEY is any key or group of keys that could become a SUPER KEY A is SECONDARY KEY is any field, or combination of fields, which does NOT yield a unique value • Used for retrieval/Narrowing purposes only • Student. Name, Student. Zip may yield several records

CIS 3465: Professor Kirs Slide Number: 19 RDBMS Basics RDBMS Keys: Each Relation MUST have a unique identifier or PRIMARY KEY A is COMPOSITE KEY is a combination of keys (Multi-key attributes) used to produce uniqueness A is SUPER KEY is either a PRIMARY or COMPOSITE KEY that uniquely identifies an entity A is CANDIDATE KEY is any key or group of keys that could become a SUPER KEY A is SECONDARY KEY is any field, or combination of fields, which does NOT yield a unique value • Used for retrieval/Narrowing purposes only • Student. Name, Student. Zip may yield several records

CIS 3465: Professor Kirs Slide Number: 20 RDBMS Basics RDBMS Keys: In Order to relate two (or more) tables FOREIGN KEYS must be used Table Student. ID Name Address Major 123456789 Saenz, Lupe 123 Mesa Finance 234567890 Chung, Mei 37 5 th St. INFOSYS 345678901 Adams, John 54 B Hague Accounting A FOREIGN KEY in one table is a PRIMARY/SUPER KEY In another Table Department Dept. Name Chairman Telephone INFOSYS Sircar, Sumit 555 -1234 Marketing Mc. Daniel, C. 555 -2345 Accounting Courtney, H. 555 -3456

CIS 3465: Professor Kirs Slide Number: 21 RDBMS Basics Database Integrity: Maintaining wholeness and Unity Entity Integrity: All Entries MUST be Unique No NULL values in primary key fields Table Student Illegal Entries Student. ID Name Address Major 123456789 Saenz, Lupe 123 Mesa Finance 234567890 Chung, Mei 37 5 th St. INFOSYS 123456789 Adams, John 54 B Hague Accounting 456789012 Elam, Mary 123 -22 E St. INFOSYS Bush, G. W. 555 Austin Marketing

CIS 3465: Professor Kirs Slide Number: 22 RDBMS Basics Database Integrity: Maintaining wholeness and Unity Entity Integrity Relational Integrity: Foreign Key MUST have a valid entry in the corresponding table (or be NULL) Table Student. ID Major 1234567890 345678901 Table Department Dept. Name Chairman Telephone Scamming INFOSYS Sircar, Sumit 555 -1234 INFOSYS Marketing Mc. Daniel, C. 555 -2345 Accounting Courtney, H. 555 -3456 ? ? ? NOT Allowed

CIS 3465: Professor Kirs Slide Number: 23 RDBMS Basics Database Integrity: Maintaining wholeness and Unity Entity Integrity Relational Integrity: Foreign Key MUST have a valid entry in the corresponding table (or be NULL) Primary Key entry CAN NOT be deleted if a foreign key refers to it Table Student Table Department Student. ID Major Dept. Name Chairman Telephone 123456789 Accounting INFOSYS Sircar, Sumit 555 -1234 234567890 INFOSYS Marketing Mc. Daniel, C. 555 -2345 345678901 Finance INFOSYS can NOT be deleted

CIS 3465: Professor Kirs Slide Number: 24 RDBMS Basics Basic RDBMS Operators: SELECT Choose All or Specific values from a table SELECT ALL Yields Given: Table Student. ID Name Address 123456789 Saenz, Lupe 123 Mesa 234567890 Chung, Mei 37 5 th St. 345678901 Adams, John 54 B Hague 456789012 Elam, Mary 123 -22 E St. 567890123 Bush, G. W 555 Austin 678901234 Clinton, B. 89 Congress 789012345 Bush, J. 555 Austin

CIS 3465: Professor Kirs Slide Number: 25 RDBMS Basics Basic RDBMS Operators: SELECT Choose All or Specific values from a table SELECT WHERE ADDRESS = “ 555 Austin” Yields Given: Table Student. ID Name Address 123456789 Saenz, Lupe 123 Mesa Student. ID Name Address 234567890 Chung, Mei 37 5 th St. 567890123 Bush, G. W 555 Austin 345678901 Adams, John 54 B Hague 789012345 Bush, J. Austin 456789012 Elam, Mary 123 -22 E St. 567890123 Bush, G. W 555 Austin 678901234 Clinton, B. 89 Congress 789012345 Bush, J. 555 Austin

CIS 3465: Professor Kirs Slide Number: 26 RDBMS Basics Basic RDBMS Operators: PROJECT Produce list of all values for a selected attribute PROJECT STUDENTID Yields Given: Table Student. ID Name Address 123456789 Saenz, Lupe 123 Mesa 234567890 Chung, Mei 37 5 th St. 345678901 Adams, John 54 B Hague 456789012 Elam, Mary 123 -22 E St. 567890123 Bush, G. W 555 Austin 678901234 Clinton, B. 89 Congress 789012345 Bush, J. 555 Austin

CIS 3465: Professor Kirs Slide Number: 27 RDBMS Basics Basic RDBMS Operators: PROJECT Produce list of of all values for a selected attribute PROJECT STUDENTID, NAME Yields Given: Table Student. ID Name Address 123456789 Saenz, Lupe 123 Mesa 234567890 Chung, Mei 37 5 th St. 345678901 Adams, John 54 B Hague 456789012 Elam, Mary 123 -22 E St. 567890123 Bush, G. W 555 Austin 678901234 Clinton, B. 89 Congress 789012345 Bush, J. 555 Austin

CIS 3465: Professor Kirs Slide Number: 28 RDBMS Basics Basic RDBMS Operators: JOIN Links 2 different tables together (Producing a different table) Each Table MUST have attributes that are common to both tables Table. A CID 123 124 125 126 127 CN AA BB CC BB DD Table. B CA 6 20 12 9 18 CR CAdd AA 123 A BB 234 B CC 345 C

CIS 3465: Professor Kirs Slide Number: 29 RDBMS Basics Basic RDBMS Operators: JOIN Consider the relationship between a Physician and his/her Patients Physician Patient Phys. ID Physname Pat. ID Patname Phys. ID 123456789 Smith, Mary 987654321 Washington, G. 234567890 Brown, John 876543210 Adams, J. Q. 345678901 Jones, Jerry 765432109 Jefferson, T. 123456789 654321098 XXX 123456789 543210987 YYY 234567890 432109876 ZZZ 345678901 321098765 AAA 123456789

CIS 3465: Professor Kirs Slide Number: 30 RDBMS Basics Basic RDBMS Operators: JOIN The Product of the tables is: Physician Phys. ID Physname 1234567890 Smith, Mary Brown, John 345678901 Jones, Jerry Patient * Pat. ID Patname Phys 9876543210 Washington, G. Adams, J. Q. 7654321098 5432109876 = Phys. ID Physname Pat. ID Patname Phys 2345678901 123456789 Smith, Mary 9876543210 Washington, G. Adams, J. Q. 2345678901 Jefferson, T. XXX 123456789 Smith, Mary 7654321098 Jefferson, T. XXX 123456789 YYY ZZZ 2345678901 123456789 Smith, Mary 5432109876 YYY ZZZ 2345678901 234567890 Brown, John 9876543210 Washington, G. Adams, J. Q. 2345678901 234567890 Brown, John 7654321098 Jefferson, T. XXX 1234567890 Brown, John 543210987 YYY 2345678901 Brown, John Jones, Jerry 43210987654321 ZZZ Washington, G. 345678901 2345678901 Jones, Jerry 876543210 Adams, J. Q. 345678901 Jones, Jerry 7654321098 Jefferson, T. XXX 12345678901 Jones, Jerry 5432109876 YYY ZZZ 2345678901 The product of a 3 (row) x 2 (column) Table and a 6 (row) x 3 (column) Table would be a 18 (row) by 5 (column) Table

CIS 3465: Professor Kirs Slide Number: 31 RDBMS Basics Basic RDBMS Operators: JOIN · Now, entering the command: Select Patname … Where Phys. ID = Phys would yield: Phys. ID Physname Pat. ID Patname Phys 123456789 Smith, Mary 9876543210 Washington, G. Adams, J. Q. 2345678901 123456789 Smith, Mary 9876543210 Washington, G. Adams, J. Q. 345678901 123456789 Smith, Mary 7654321098 Jefferson, T. XXX 123456789 123456789 Smith, Mary 5432109876 YYY ZZZ 2345678901 234567890 Brown, John 9876543210 Washington, G. Adams, J. Q. 2345678901 234567890 Brown, John 7654321098 Jefferson, T. XXX 123456789 234567890 Brown, John 543210987 YYY 234567890 345678901 Brown, John Jones, Jerry 43210987654321 ZZZ Washington, G. 345678901 2345678901 Jones, Jerry 876543210 Adams, J. Q. 345678901 345678901 Jones, Jerry 7654321098 Jefferson, T. XXX 123456789 345678901 Jones, Jerry 5432109876 YYY ZZZ 2345678901

CIS 3465: Professor Kirs Slide Number: 32 RDBMS Basics Basic RDBMS Operators: JOIN • Natural Joins (Also called Inner Joins) • ONLY the rows with common values in their join columns are retained. Table. A CID 123 124 125 126 127 CN AA BB CC BB DD Table. C Table. B CA 6 20 12 9 18 CR CAdd AA 123 A BB 234 B CC 345 C CID 123 124 126 125 CN CA CAdd AA 6 123 A BB 20 234 B BB 9 234 B CC 12 345 C

CIS 3465: Professor Kirs Slide Number: 33 RDBMS Basics How? ? ? A Simple PRODUCT of the Tables is First created Table. A CID 123 124 125 126 127 CN AA BB CC BB AA Table. B CA 6 20 12 9 18 CR CAdd AA 123 A BB 234 B CC 345 C CID 123 124 125 126 127 CN AA BB CC BB AA CA 6 20 12 9 18 CR AA AA AA BB BB BB CC CC CC CAdd 123 A 123 A 234 B 234 B 345 C 345 C CR AA Joins CR BB Joins CR CC Joins

CIS 3465: Professor Kirs Slide Number: 34 RDBMS Basics How? ? ? A Simple PRODUCT of the Tables is First created A SELECT command where CN = CR is issued CID 123 124 125 126 127 CN AA BB CC BB AA CA 6 20 12 9 18 CR AA AA AA BB BB BB CC CC CC CAdd 123 A 123 A 234 B 234 B 345 C 345 C Table. C CID 123 124 126 125 CN CA CAdd AA 6 123 A BB 20 234 B BB 9 234 B CC 12 345 C

CIS 3465: Professor Kirs Slide Number: 35 RDBMS Basics ADDITIONAL RDBMS Operators: (NOT always available) UNION May use single table Combines the results of two unrelated queries into a single output E. G. , Creating a phone directory from 2 separate tables There are no common values so a join can not be performed Separate queries will produce separate tables Duplicate records NOT displayed

CIS 3465: Professor Kirs Slide Number: 36 RDBMS Basics ADDITIONAL RDBMS Operators: (NOT always available) INTERSECT Similar to UNION (Combines the results of two unrelated queries into a single output) There MUST be matching rows in the tables E. G. , Creating a phone directory of Faculty with offices in BUS who also teach in the Business Bldg. Some Faculty Have offices in BUS Some Faculty Teach in the Business Building Merge those who do both into one listing

CIS 3465: Professor Kirs Slide Number: 37 RDBMS Basics ADDITIONAL RDBMS Operators: (NOT always available) DIFFERENCE Yields all rows in one table NOT found in the other PRODUCT (Seen Earlier) Produces list of all possible pairings DIVIDE

CIS 3465: Professor Kirs Slide Number: 38 RDBMS Basics RDBMS Software Classifications Fully Relational Supports all relational algebra functions Enforces Entity and Referential Integrity Rules Relationally Complete Supports all relational algebra functions Does NOT support Integrity Rules Minimally Relational Supports only SELECT, PROJECT, and JOIN Tabular Supports only SELECT, PROJECT, and JOIN User MUST define all access paths

CIS 3465: Professor Kirs Slide Number: 39 RDBMS Basics