1 / 74

Unit Reading Assignment

BIS4435 – Data Management for Decision Support Lecture 3: Normalization and Database Query Dr. Nawaz Khan School of Computing Science E-mail: n.x.khan@mdx.ac.uk. Unit Reading Assignment. Unit 5: Entity Relationship Modelling: A Traditional vs. Object Oriented Approach

cameron-cote
Download Presentation

Unit Reading Assignment

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BIS4435 – Data Management for Decision SupportLecture 3: Normalization and Database QueryDr. Nawaz KhanSchool of Computing ScienceE-mail: n.x.khan@mdx.ac.uk

  2. Unit Reading Assignment • Unit 5:Entity Relationship Modelling: A Traditional vs. Object • Oriented Approach • Reading Suggestion: • Connolly, T.M., and Begg, C.E., Database Systems: A Practical • Approach to Design, Implementation and Management, Addison-Wesley, • 4th Edition, ISBN: 0321210255(chapters 6) • Global campus materials on OASIS: http://oasis.mdx.ac.uk/ (unit 5) • More Reading: Fundamentals of Database Systems. R. Elmasri and S. B. Navathe, 4th Edition, 2004, Addison-Wesley, ISBN 0-321-12226-7 (Chapters 10)

  3. Unit Learning Outcome At the end of this unit you should be able to: • Describe the process, strengths and weaknesses of Data Normalisation, and so demonstrate an understanding of when and to what extent the technique should be applied in practice • Explain and apply the concepts of Functional Dependency and Determinant through the understanding and construction of Determinacy Diagrams • Describe and apply understanding of three Normal Forms for relations: • Convert Un-normalised data into First Normal Form relations, so that data items contain only single, simple values • derive Second Normal Form relations by eliminating part-key dependencies • derive Third Normal Form relations by removing transitive dependencies • Convert a set of relations to Boyce-Codd Normal form

  4. Functional Dependencies and Normalization for Relational Databases • Outline • Introduction • Functional dependency • Direct dependency • Indirect dependency • Partial dependency • Normalization • 1NF and dependency problems • 2NF – solves partial dependency • 3NF – solves indirect dependency • BCNF – well-normalized relations • Notes and suggestions • Summary

  5. Functional Dependencies and Normalization for Relational DatabasesIntroduction • Each relation schema consists of a number of attributes and the relational database schema consists of a number of relation schemas • Attributes are grouped to form a relation schema • Need some formal measure of why one grouping of attributes into a relation schema may be better than another

  6. Functional Dependencies and Normalization for Relational DatabasesIntroduction • Top-down database design: Requirements Mini-world Conceptual schema ? E1 R Relation schemas E2

  7. Functional Dependencies and Normalization for Relational DatabasesIntroduction • “Goodness” measures: • Redundant information in tuples • Update anomalies: modification, deletion, insertion • Reducing the NULL values in tuples • Disallowing the possibility of generating spurious tuples

  8. Functional Dependencies and Normalization for Relational DatabasesIntroduction • Redundant information in tuples • In EMP_DEPT, the attribute values pertaining to a particular department (DNUMBER, DNAME, DMGRSSN) are repeated for every employee who works for that department • Update anomalies: modification, deletion, insertion • Modification • As the manager of a dept. changes we have to update many values according to employees working for that dept. • Easy to make the DB inconsistent

  9. Functional Dependencies and Normalization for Relational DatabasesIntroduction • Update anomalies: modification, deletion, insertion (cont.) • Deletion: • If Borg James E. leaves, we delete his tuple and lose: • the existing of dept. 1 • the name of dept. 1 • who is the manager of dept. 1 • Insertion: • How can we create a department before any employees are assigned to it ??

  10. Functional Dependencies and Normalization for Relational DatabasesIntroduction • Reducing the NULL values in tuples • Employees not assigned to any dept.: waste the storage space • Other difficulties: aggregation operations (e.g., COUNT) and joins • Disallowing the possibility of generating spurious tuples EMP_PROJ(SSN, PNUMBER, HOURS, ENAME, PNAME, PLOCATION) EMP_LOCS(ENAME, PLOCATION) EMP_PROJ1(SSN, PNUMBER, HOURS, PNAME, PLOCATION) • Generation of invalid and spurious data during JOINS: PLOCATION is the attribute that relates EMP_LOCS and EMP_PROJ1, and PLOCATION is neither a primary key nor a foreign key in either EMP_LOCS or EMP_PROJ1 (cf. chapter 10 [1] for more details)

  11. Functional Dependencies and Normalization for Relational DatabasesIntroduction • “Goodness” measures: • Redundant information in tuples • Update anomalies: modification, deletion, insertion • Reducing the NULL values in tuples • Disallowing the possibility of generating spurious tuples  Normalization • It helps DB designers determine the best relation schemas • A formal framework for analyzing relation schemas based on their keys and on the functional dependencies among their attributes • A series of normal form tests that can be carried out on individual relation schemas so that the relational database can be normalized to any desired degree • It is based on the concept of normal form 1NF, 2NF, 3NF, BCNF, 4NF, 5 NF • It is a process which ensures that the data is structured in such a way that attributes are grouped with the PK. Attributes that do not directly depend on PK may be extracted to form a new relation

  12. Functional Dependencies and Normalization for Relational DatabasesFunctional dependency • Dependencies between different ATTRIBUTES - determinant • Direct dependency • Indirect dependency • Partial dependency Conceptual schema E1 R E2 ? Relation schemas

  13. Functional Dependencies and Normalization for Relational DatabasesFunctional dependency • Determinant: • X is a determinant of Y if each value of X has precisely one (possibly NULL) associated value of Y • Another way: X is a determinant of Y if and only if whenever two tuples agree on their X value they agree on their Y value • X determines Y or Y is functionally dependent on X • Formal definition: • A functional dependency, denoted by X ->Y, between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuples that can form a relation state r of R. The constraint is that, for any two tuples t1 and t2 in r that have t1[X] = t2[X], we must also have t1[Y] = t2[Y] • Functional determinancy diagram shows the dependencies in a visual format X Y

  14. Functional Dependencies and Normalization for Relational DatabasesFunctional dependency • An example: • Direct dependency (fully functional dependency) • All attributes in a R must be fully functionally dependent on the primary key (or the PK is a determinant of all attributes in R) Performer-name Performer-id Performer-type Performer-location

  15. Functional Dependencies and Normalization for Relational DatabasesFunctional dependency • Indirect dependency (transitive dependency) • Value of an attribute is not determined directly by the primary key Performer- Performer- name id Performer- Fee type Performer- location

  16. Functional Dependencies and Normalization for Relational DatabasesFunctional dependency • Partial dependency • Composite determinant - more than one value is required to determine the value of another attribute, the combination of values is called a composite determinant EMP_PROJ(SSN, PNUMBER, HOURS, ENAME, PNAME, PLOCATION) {SSN, PNUMBER} -> HOURS • Partial dependency - if the value of an attribute does not depend on an entire composite determinant, but only part of it, the relationship is known as the partial dependency SSN -> ENAME PNUMBER -> {PNAME, PLOCATION}

  17. Functional Dependencies and Normalization for Relational DatabasesFunctional dependency • Partial dependency • Another example using functional determinancy diagram: Performer-name Performer-id Performer-type Performer-location Fee Agent-name Agent-id Agent-location

  18. Functional Dependencies and Normalization for Relational Databases • Outline • Introduction • Functional dependency • Direct dependency • Indirect dependency • Partial dependency • Normalization • 1NF and dependency problems • 2NF – solves partial dependency • 3NF – solves indirect dependency • BCNF – well-normalized relations • Notes and suggestions • Summary BIS4229 – Industrial Data Management Technologies

  19. Functional Dependencies and Normalization for Relational DatabasesNormalization – 1NF • First normal form (1NF) - there is only one value at the intersection of each row and column of a relation - no set valued attributes in 1 NF • To be part of the formal definition of a relation in the basic (flat) relational model • Problem with 1NF - no primary key for the relation

  20. Functional Dependencies and Normalization for Relational DatabasesNormalization – 1NF Performer-name Performer-location Performer-id Performer-type Fee Agent-name Agent-id Agent-location Venue-name 1NF determinancy diagram Venue-id Venue-location Event-name Event-id Event-type Booking-date

  21. Functional Dependencies and Normalization for Relational DatabasesNormalization – 2NF • Second normal form (2NF) - all attributes must be fully functionally dependent on the primary key • 2NF solves partial dependency problem in 1NF • Method: identify primary keys and group attributes that relate to the key together to form separate new relations • Benefit of 2NF • Problem with 2NF

  22. Functional Dependencies and Normalization for Relational DatabasesNormalization – 2NF Relation in 2NF P- type P-id P-name Fee P-loc’n 101 Baron Singer York 75 105 Steed Dancer 60 Berlin 108 Jones Actor 85 Bombay 112 Eagles Actor 85 Leeds 118 Markov Dancer 60 Moscow 126 Stokes Comedian 90 Athens 129 Chong Actor 85 Beijing 134 Brass Singer 75 London 138 Ng Singer 75 Penang 140 Strong Magician 72 Rome 141 Gomez Musician 92 Lisbon 143 Tan Singer 75 Chicago 147 Qureshi Actor 85 London 149 Tan Actor 85 Taipei 150 Pointer Magician 72 Paris 152 Peel Dancer 60 London Performer-name Performer-id Performer-type Fee Performer-location 2NF determinancy diagram

  23. Functional Dependencies and Normalization for Relational DatabasesNormalization – 2NF A-name A- loc’n A-id 1295 Burton Lonton 1435 Boston Nunn 1504 Lee Taipei 1682 Tsang Beijing Agent-name 1460 Stritch Rome Agent-id 1522 Ellis Madrid 1509 Patel York Agent-location 1478 Burns Leeds 1377 Webb Sydney 1478 Burns Leeds 1190 Patel Hue 1802 Chapel Bristol 1076 Eccles Oxford 1409 Arkley York 1428 Vemon Cairo V-name V-id V-loc’n Venue-name 59 Atlas Tokyo Venue-id 35 Polis Athens 54 Nation Lisbon 17 Silbury Tunis Venue-location 46 Royale Cairo 75 Vostok Kiev 79 Festive Rome 28 Gratton Boston 84 State Kiev 82 Tower Lima 92 Palace Milan 62 Shaw Oxford 2NF determinancy diagram Relation in 2NF

  24. Functional Dependencies and Normalization for Relational DatabasesNormalization – 2NF E-id E-type E-name 959 Show Time Musical 907 Elgar 1 Concert 921 Silver Shoe Ballet 942 White Lace Ballet 901 The Dark Drama 913 What Now Drama Event-type 926 Next Year Drama Event-id 952 Gold Days Drama 934 Angels Opera 945 Trick-Treat Variety show Event-name 938 New Dawn Drama 981 Birdsong Musical 957 Quicktime Musical 963 Vanish Magic show 941 Mahler 1 Concert 964 The Friends Drama 927 Chanson Opera 971 Card Trick Magic show 988 Secret Tape Drama 978 Swift Step Dance Relation in 2NF 2NF determinancy diagram

  25. Functional Dependencies and Normalization for Relational DatabasesNormalization – 2NF Performer-id E-id Booking-date P-id A-id V-id Agent-id Booking-date 101 1295 59 959 25-Nov-99 105 1435 35 921 07-Jan-02 105 1504 54 942 10-Feb-02 Venue-id 108 1682 79 901 29-Jul-03 112 1460 17 926 13-Aug-00 112 1522 46 952 05-May-99 112 1504 75 952 16-Mar-99 126 1509 59 945 02-Sept-01 Event-id 129 1478 79 926 22-Jun-00 134 1504 28 981 18-Sept-01 138 1509 84 957 18-Aug-99 140 1478 17 963 18-Aug-99 141 1478 84 941 21-Jul-00 143 1504 79 927 21-Nov-02 147 1076 17 952 30-Apr-00 147 1409 79 988 17-Apr-00 152 1428 59 978 01-Oct-01 2NF determinancy diagram Relation in 2NF

  26. Functional Dependencies and Normalization for Relational DatabasesNormalization – 2NF Performer-name Performer-id Performer-type Fee Relation in 2NF Performer-location P- type P-id P-name Fee P-loc’n 101 Baron Singer York 75 105 Steed Dancer 60 Berlin 108 Jones Actor 85 Bombay 112 Eagles Actor 85 Leeds 118 Markov Dancer 60 Moscow 126 Stokes Comedian 90 Athens 129 Chong Actor 85 Beijing 134 Brass Singer 75 London 138 Ng Singer 75 Penang 140 Strong Magician 72 Rome 141 Gomez Musician 92 Lisbon 143 Tan Singer 75 Chicago 147 Qureshi Actor 85 London 149 Tan Actor 85 Taipei 150 Pointer Magician 72 Paris 152 Peel Dancer 60 London 2NF determinancy diagram • Problem with 2NF: - Insertion • - Modification • - Deletion

  27. Functional Dependencies and Normalization for Relational DatabasesNormalization – 3NF • Third normal form (3NF) - all attributes must be directly dependent on primary key • 3NF solves indirect (transitive) dependencies problem in 1NF and 2NF • Method: identify all transitive dependencies and each transitive dependency will form a new relation, with non-prime attributes participating in the transitive dependency and the attribute which determines others as the attributes for the new relation

  28. Functional Dependencies and Normalization for Relational DatabasesNormalization – 3NF P- type P-id P-name P-loc’n 101 Baron Singer York 105 Steed Dancer Berlin 108 Jones Actor Bombay Performer- Performer-id 112 Eagles Actor Leeds name 118 Markov Dancer Moscow 126 Stokes Comedian Athens Performer- 129 Chong Actor Beijing type 134 Brass Singer London 138 Ng Singer Penang 140 Strong Magician Rome 141 Gomez Musician Lisbon Performer- 143 Tan Singer Chicago location 147 Qureshi Actor London 149 Tan Actor Taipei 150 Pointer Magician Paris 152 Peel Dancer London P- type Fee Singer 75 Dancer 60 Performer- Fee Actor 85 type Comedian 90 Magician 72 Musician 92 3NF determinancy diagram Relation in 3NF

  29. Functional Dependencies and Normalization for Relational DatabasesNormalization Event-type Agent-name Event-id Agent-id Agent-location Event-name Agent-location Performer-id Venue-name Venue-id Agent-id Booking-date Venue-location Venue-id Event-id 3NF determinancy diagram

  30. Functional Dependencies and Normalization for Relational DatabasesNormalization - BCNF • Boyce-Codd normal form (BCNF) was proposed as a simpler form of 3NF, but it was found to be stricter than 3NF, because every relation in BCNF is also in 3NF; however, a relation in 3NF is not necessarily in BCNF • BCNF: well-normalized relations • The formal definition of 3NF: A relation schema R is in third normal form (3NF) if, whenever a nontrivial functional dependency X->A holds in R, either (a)X is a superkey of R, or (b)A is a prime attribute of R • The formal definition of BCNF differs slightly from the definition of 3NF: A relation schema R is in BCNF if whenever a nontrivial functional dependency X -> A holds in R, then X is a superkey of R • More details: chapter 10 [1]

  31. Activity BIS4229 – Industrial Data Management Technologies

  32. Solution • Proj_num proj_name • Emp_num emp_name job_class • Emp_num proj_num assign_hours • Job_class chg_hours

  33. Lecture 4: Introduction to SQL and OQLDr. Nawaz KhanSchool of Computing ScienceE-mail: n.x.khan@mdx.ac.uk

  34. Unit Reading Assignment Unit 4:Introduction to SQL and OQL: Relational and Object oriented Approach • Reading Suggestion: • Global campus materials on OASIS: http://oasis.mdx.ac.uk/ (unit 4) • Connolly, T.M., and Begg, C.E., Database Systems: A Practical Approach to Design, Implementation and Management, Addison-Wesley, 4th Edition, ISBN: 0321210255(chapters 5 and 6)

  35. Unit Learning Outcomes • Apply SQL statements in Oracle to perform database query transactions and various calculations • Apply SQL commands in Oracle (CREATE) to define table structure in a relational database and to manipulate rows in a table • Apply SQL statements in Oracle (GRANT and REVOKE) to control data access • Apply Object Definition Language (ODL) to create data objects for data abstraction and nested tables • Apply OQL statements in Oracle to execute queries on data objects

  36. Introduction to SQL and OQL • Outline • Introduction to SQL • Data Definition Language (DDL) • Create table • Alter table • Drop table • Data Manipulation Language (DML) • Retrieval • SELECT-FROM-WHERE (the S-F-W structure) • Aggregate functions and grouping • Update operations: insert, delete, update • More complex features of SQL queries/commands • Object Query in Oracle

  37. Structured Query Language (SQL)Introduction to SQL • Two formal languages for the relational model: • The relational algebra • Provides a formal foundation for relational model operations • Is used as a basis for implementing and optimizing queries in RDBMSs (relational database management systems) • Some of its concepts are incorporated into the SQL standard query language for RDBMSs • The user must specify HOW- that is, in what order-to execute the query operations • The relational calculus: • Provides a higher-level declarative notation for specifying relational queries • In relational calculus, we write one declarative expression to specify a retrieval request, and hence there is no description of how to evaluate a query • A calculus expression specifies WHAT is to be retrieved rather than HOW to retrieve it -> nonprocedural language

  38. Structured Query Language (SQL)Introduction to SQL • SQL: although it includes some features from relational algebra, it is based to a greater extent on the (tuple) relational calculus. However the SQL syntax is more user-friendly than either of the two formal languages • SQL: Structured Query Language • The standard language for commercial relational DBMSs • ANSI 1986: SQL1, or SQL-86 • SQL2, or SQL-92 (revised version of SQL1) • SQL3, or SQL-99: core specification + optional specialized packages (for specific database applications such as data mining, spatial data, temporal data, data warehousing, OLAP, multimedia data, etc.)

  39. Structured Query Language (SQL)Introduction to SQL • SQL is very good for querying and manipulating data in database systems. • SQL lacks some features commonly found in general programming languages, such as LOOP and IF-THEN-ELSE statements • SQL consists of three major components: • DDL (data definition language) to define the way in which data is stored • DML (data manipulation language) to allow retrieval, insertion of data etc. (this is sometimes called the 'query ' language) • DCL (data control language) to define activities that do not fit with the other two activities

  40. Structured Query Language (SQL)Introduction to SQL • SQL consists of three major components (cont.): • The Data Manipulation Language (DML) of SQL allows the retrieval, insertion, update and removal of rows stored in relational tables. As mentioned above, numbers of rows can be altered in any one statement, and so DML is a very powerful tool • The Data Definition Language (DDL) is used to create, change the structure or remove whole tables and other relational structures. So whereas you would use the INSERT statement of the DML to insert new rows into an existing table, you would use the DDL CREATE TABLE statement to establish a new table in the first place • The Data Control Language (DCL) define activities that are not in the categories of those for the DDL and DML, such as granting privileges to users, and defining when proposed changes to a databases should be irrevocably made

  41. Structured Query Language (SQL)Introduction to SQL • Our concerns during this lecture: • The Data Manipulation Language (DML) • The Data Definition Language (DDL) • The Data Control Language (DCL) • Objective: How SQL is capable of: • Creating, changing the structure or removing whole tables and other relational structures • Allowing the retrieval, insertion, update and removal of rows/tuples stored in relational tables

  42. Structured Query Language (SQL)Introduction to SQL • The following example is used throughout this lecture to illustrate SQL operations: • Suppose we have a university database containing the following relations: STUDENT (SNO, SNAME, SLEVEL) COURSE (CNO, TITLE, LNAME) REPORT (SNO, CNO, MARK) LECTURER (LNAME, ROOM_NO) • Remember: • A relation schema may be viewed as a table. • An instance of a relation may be viewed as a set consisting of tuples

  43. Structured Query Language (SQL) Data Definition Language (DDL) • Create table • In terms of SQL statements, a relation is implemented as a table. Subsequently, a tuple is a row in a table and an attribute is a column of a table • The CREATE TABLE command is used to set up a new table (i.e. to implement a relation) in the database. The table must be given a name (corresponding to the name of the relation) and its attributes must be named and given data types • Example: To create the table (i.e. relation) STUDENT with the attributes SNO of type INTEGER, SNAME of type VARCHAR, and SLEVEL of type INTEGER, the following SQL statement is used: CREATE TABLE STUDENT (SNO INTEGER, SNAME VARCHAR(20), SLEVEL INTEGER); • Note: The data types used in the above example are those allowed in Oracle SQL. Data types in different implementations of SQL may vary slightly in that they may use different names for a particular type

  44. Structured Query Language (SQL) Data Definition Language (DDL) • A number of important constraints can be defined on attributes during table creation: • NOT NULL: which means that the value of the attribute must not be NULL. This can be specified for any attribute as required • DEFAULT: which is used to specify a default value for the attribute. If there is no default value specified, the system default value is NULL • Example: In the above example, if assume SNO is a key attribute whose value cannot be null, and a default value for SLEVEL is 0, we have the following SQL statement: CREATE TABLE STUDENT (SNO INTEGERNOT NULL, SNAME VARCHAR(20), SLEVEL INTEGERDEFAULT 0); • Note: If no default value is specified and NULL value is not allowed, then a value must be supplied by the user. Otherwise, an error occurs • Similarly, write SQL commands to create COURSE, REPORT, and LECTURER tables

  45. Structured Query Language (SQL) Data Definition Language (DDL) • Create table (Implementation of Primary Key and Foreign Key) CREATE TABLE ARTIST (ARTIST_ID NUMBER(2), ARTIST_NAME TEXT(30), COUNTRY_OF_ORIGIN TEXT(25), DATE_OF_BIRTH DATE, PRIMARY KEY (ARTIST_ID)); CREATE TABLE MUSIC_COLLECTION (ITEM_ID NUMBER(4), TITLE VARCHAR(40), ARTIST_ID NUMBER(2), ITEM_TYPE TEXT(1), DATE_PURCHASED DATE, PRIMARY KEY (ITEM_ID), CONSTRAINT FK_ARTIST FOREIGN KEY (ARTIST_ID) REFERENCES ARTIST (ARTIST_ID)); • ON DELETE SET NULL and ON DELETE CASCADE to retain data integrity

  46. Structured Query Language (SQL) Data Definition Language (DDL) • Alter table • The ALTER statement in SQL, like the CREATE statement, can be used to change a number of different types of data object, including tables, access privileges, constraints etc. Here we shall concentrate on its use to change the structure of tables. • You can use the ALTER TABLE statement to modify a table's definition. This statement changes the structure of a table, not its contents. You can use the ALTER TABLE statement to: • Add a new column to an existing table: For example, to add a department head attribute to the DEPT table we could specify: ALTER TABLE DEPT ADD DEPT_HEAD NUMBER(4); • Increase or decrease the width of an existing column: to change an EMP table so that the DEPTNO attribute can contain 3 digit values: ALTER TABLE EMP MODIFY DEPTNO NUMBER(3); • Change an existing column from mandatory to optional  (i.e. specify that it may contain nulls), etc. (homework !!)

  47. Structured Query Language (SQL) Data Definition Language (DDL) • Drop table • To remove a table, the DDL statement is: DROP TABLE <TABLE NAME> • It is deceptively easy to issue this command, and unlike most systems one encounters today, there is no prompt at all about whether you wish to proceed with the process. Dropping a table involves the removal of all the data and constraints on the table, and finally removal of the table structure itself • Example to remove our copy of the EMP table:       DROP TABLE EMP; • Using the DROP TABLE statement when creating tables

  48. Structured Query Language (SQL) Data Manipulation Language (DML) • Basic SQL queries: The basic SQL query statement takes the so-called S-F-W form: SELECT <attribute list> FROM <table list> WHERE <condition>; where: <attribute list> is a list of attribute names whose values are to be retrieved by the query. <table list> is a list of the relation names from which the attributes to be retrieved. <condition> is a Boolean expression to specify the condition that the tuples to be retrieved have to satisfy

  49. Structured Query Language (SQL) Data Manipulation Language (DML) STUDENT (SNO, SNAME, SLEVEL) COURSE (CNO, TITLE, LNAME) REPORT (SNO, CNO, MARK) LECTURER (LNAME, ROOM_NO) • Query 1: Retrieve the numbers and names of all students at level 1: SELECT SNO, SNAME FROM STUDENT WHERE SLEVEL = 1; • Note: The result of this query is a "relation" containing the numbers and names of the level 1 students

  50. Structured Query Language (SQL) Data Manipulation Language (DML) • Qualifying attribute names: If two or more tables (i.e. relations) are involved in the query, the attribute names must be qualified by prefixing the relation names to the attribute names • Query 2: Retrieve the numbers and names of students who take course 101. SELECT STUDENT.SNO, STUDENT.SNAME FROM STUDENT, REPORT WHERE STUDENT.SNO = REPORT.SNO AND REPORT.CNO = 101; • Note: Query 2 performs a join operation between STUDENT and REPORT on SNO

More Related