Giving names to constraints keyword constraint name a

Info icon This preview shows pages 22–37. Sign up to view the full content.

Giving Names to Constraints Keyword CONSTRAINT Name a constraint Useful for later altering Database Design -- Basic SQL 22
Image of page 22

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Constraints Database Design -- Basic SQL 23
Image of page 23
Specifying Constraints on Tuples Using CHECK CHECK clauses at the end of a CREATE TABLE statement Apply to each tuple individually CHECK (Dept_create_date <= Mgr_start_date); Database Design -- Basic SQL 24
Image of page 24

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Basic Retrieval Queries in SQL SELECT statement One basic statement for retrieving information from a database SQL allows a table to have two or more tuples that are identical in all their attribute values Unlike relational model Multiset or bag behavior (sets don’t allow identical members) Database Design -- Basic SQL 25
Image of page 25
The Structure of Basic SQL Queries Database Design -- Basic SQL 26 Basic form of the SELECT statement:
Image of page 26

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

The Structure of Basic SQL Queries Logical comparison operators =, <, <=, >, >=, and <> Projection attributes Attributes whose values are to be retrieved Selection condition Boolean condition that must be true for any retrieved tuple Database Design -- Basic SQL 27
Image of page 27
Some Queries Database Design -- Basic SQL 28
Image of page 28

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Some Queries Database Design -- Basic SQL 29
Image of page 29
Ambiguous Attribute Names Same name can be used for two (or more) attributes As long as the attributes are in different relations Must qualify the attribute name with the relation name to prevent ambiguity Database Design -- Basic SQL 30
Image of page 30

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Aliasing, Renaming, and Tuple Variables Aliases or tuple variables Declare alternative relation names E and S EMPLOYEE AS E(Fn, Mi, Ln, Ssn, Bd, Addr, Sex, Sal, Sssn, Dno) Query 8. For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor. SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.Super_ssn=S.Ssn; Recommended practice to abbreviate names and to prefix same or similar attribute from multiple tables Database Design -- Basic SQL 31
Image of page 31
Aliasing, Renaming, and Tuple Variables The attribute names can also be renamed EMPLOYEE AS E(Fn, Mi, Ln, Ssn, Bd, Addr, Sex, Sal, Sssn, Dno) Note that the relation EMPLOYEE now has a variable name E which corresponds to a tuple variable The “AS” may be dropped in most SQL implementations Database Design -- Basic SQL 32
Image of page 32

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Unspecified WHERE Clause and Use of the Asterisk Missing WHERE clause Indicates no condition on tuple selection CROSS PRODUCT All possible tuple combinations Database Design -- Basic SQL 33
Image of page 33
Unspecified WHERE Clause and Use of the Asterisk Specify an asterisk (*) Retrieve all the attribute values of the selected tuples Database Design -- Basic SQL 34
Image of page 34

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Tables as Sets in SQL SQL does not automatically eliminate duplicate tuples in query results Use the keyword DISTINCT in the SELECT clause Only distinct tuples should remain in the result Database Design -- Basic SQL 35
Image of page 35
Tables as Sets in SQL Set operations UNION , EXCEPT (difference), INTERSECT Corresponding multiset operations: UNION ALL , EXCEPT ALL , INTERSECT ALL ) Database Design -- Basic SQL 36
Image of page 36

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Image of page 37
This is the end of the preview. Sign up to access the rest of the document.
  • Fall '09
  • SUNANHAN
  • Relational model

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern