HomeWork in DB
SamG*
Access: Create a Table from a Query, Eg 31
SELECT *
INTO SmallCust3
FROM Customer
WHERE CreditLimit <= 7500;
- INTO clause used to save the results of a query into a new table
- Specified before FROM and WHERE clauses
- SmallCust3 table did not exist before INTO
- Data added to the new table is separate
- if change in SmallCust3, no change in Customer
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Figures 3.59-3.60: SQL Query to Create New Table
*
Access: Create a Table from a Query
- Can query newly created table like any other table.
SELECT SmallCust3.CustomerNum
FROM SmallCust3;
- Useful if have complex query:
- Break up into two parts
- Do 1st part and store into table
- Then do 2nd part as query on stored table.
- Can do same with views or saved query in Access
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Make-Table : QBE version of INTO. Eg: 2_18
- Create new table with customer# and cname, and num, fname, lname of of customer’s sales rep.
- Do as normal SELECT query and then choose Make-Table from Query.
- SQL:
SELECT Customer.CustomerNum, Customer.CustomerName, Customer.RepNum, Rep.FirstName, Rep.LastName INTO CustomerRep2
FROM Rep INNER JOIN Customer ON Rep.RepNum = Customer.RepNum;
CS 622/215 Databases
Winter 14, Week 6
*
*
Figure 2.37: Make-Table Query
*
Figure 2.39: Make-Table Query (con’t.)
*
Views
CREATE VIEW REDPARTS AS
SELECT *
FROM P
WHERE COLOR = ‘RED’;
- View are virtual tables
- Not physically stored in database
- Created on demand
- Created via view definition
- Looks just like a table
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Query on a View
- Views can be queried just like relations
- How is this done ?
- Query modification: DBMS changes a query on a view to a query on underlying table.
SELECT * FROM REDPARTS
WHERE WEIGHT > 15;
is converted to
SELECT * FROM P
WHERE COLOR = ‘RED’ AND WEIGHT > 15;
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Creating Views/Saving Queries in Access
- Microsoft Access does not support views, but can get similar functionality by saving the query
- can write another query on saved query
Create a query and save it. Eg: with the name: TempQuery
- TempQuery can be queried as if it was a table.
- SELECT * FROM TempQuery;
- Query402 : SELECT RepNum FROM eg10;
- Like INTO, a way of breaking up complex queries.
- Can use queries like tables when building new queries in QBE also
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Figures 4.3 - 4.4: Access Query Design of View
*
Eg:INTO: alternative to Nested From: warehouse with largest #parts
- Save “WPCount” in a query/temporary table:
SELECT Warehouse AS W, COUNT (PartNum) AS NumberofParts
INTO WPcount FROM Part
GROUP BY Warehouse;
- Now get warehouse with largest count: SELECT W FROM WPcount
WHERE WPcount.NumberofParts =
(SELECT MAX (NumberofParts)
FROM WPcount);
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Eg: Another way to do same thing
- Save “WPCount” query as query402
SELECT Warehouse AS W, COUNT (PartNum) AS NumberofParts
FROM Part
GROUP BY Warehouse;
- Now get warehouse with largest count:
SELECT W FROM query402
WHERE query402.NumberofParts =
(SELECT MAX (NumberofParts)
FROM query402);
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Count Distinct Eg
- How many customers have placed an order
SELECT COUNT(DISTINCT CustomerNum ) FROM Orders;
- Access does not support DISTINCT with aggregate ops. Eg: above will not work. How to do ?
- First do DISTINCT and store in temp table (query)
- Then run aggregate op query on temp table
SELECT DISTINCT CustomerNum FROM Orders;
- Save query as query55, then do aggregate operation:
SELECT COUNT (CustomerNum) FROM query55;
- This will work in Access
CS 622/215 Databases
Winter 14, Week 6
*
7
*
Views are dynamic
- Any change in underlying base table gets reflected in the view.
CREATE VIEW REDPARTS AS
SELECT *FROM P WHERE COLOR = ‘RED’;
- Suppose initially 2 red parts in P
SELECT * FROM REDPARTS How many rows?
- 2 rows. Now insert one new red part into P.
SELECT * FROM REDPARTS How many rows?
- 3 rows. Why ?
- Query modification. This query becomes:
SELECT * FROM P WHERE COLOR = ‘RED’ ;
CS 622/215 Databases
Winter 14, Week 6
*
10
*
What kinds of view can be built?
- Subset of rows
- Subset of columns
- Contain derived attributes
- Renaming of columns
- Can be from multiple tables
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Views : subset of columns
- Column names can be given explicitly or be inherited from base table
CREATE VIEW
SOMERED (P#, PARTNAME, WT)
AS SELECT P#, PNAME, WEIGHT
FROM P
WHERE COLOR = ‘RED’;
*
More on views
- Column names must be explicitly stated if derived
- Derived columns
CREATE VIEW
PARTQUANT (PNO, TOTALQT)
AS SELECT P#, SUM (QTY)
FROM SP GROUP BY P# ;
- Can be used to create other views
CREATE VIEW
HEAVYREDPARTS(P#, PARTNAME)
AS SELECT P#, PARTNAME
FROM SOMERED WHERE WT > 13;
CS 622/215 Databases
Winter 14, Week 6
*
10
*
E.R. (entity relationship model)
- High level data model: conceptual design stage
- Not an implementation model
- Independent of lower level implementation details
- What the tables look like won’t impact ER schema
- What is it good for : can communicate what world looks like by capturing
- What are the different types of data
- The relationships between them.
- In an easy to understand language
- Helps in relational design
CS 622/215 Databases
Winter 14, Week 6
*
7
*
Elmasri Company Database
- The company is organized into DEPARTMENTS.
- Each department has a name, number and an employee who manages the department
- We keep track of the start date of the department manager
- Departments can have multiple locations
- Each department controls a number of PROJECTs.
- Each project has a name, number and is located at a single location.
CS 622/215 Databases
Winter 14, Week 6
*
7
*
Elmasri Company Database
- For each EMPLOYEE, we store the social security number, address, salary, sex, and birthdate.
- Employees may have a supervisor
- Each employee works for one department but may work on several projects.
- We keep track of the number of hours per week that an employee currently works on each project.
- Each employee may have a number of DEPENDENTs.
- For each dependent, we keep track of their name, sex, birthdate, and relationship to employee.
CS 622/215 Databases
Winter 14, Week 6
*
7
*
ER model basics
- Entities: things (“real world objects”) distinguishable from other things.
- Attributes: properties of entities
- Relationships: between entities
- Have to figure out
What are the entities and relationships ?
What information about these entities and relationships should we store in the database?
What are the integrity constraints or business rules that hold?
Can map an ER diagram into a relational schema.
CS 622/215 Databases
Winter 14, Week 6
*
7
*
Entities
- Entities: a thing with an independent existence. Eg : people, objects, events etc
- Eg: Each of the employees Alice, Bill, Mike is an entity
- Eg: Is Payroll an entity ?
- Eg: Is the social security number 123456789 an entity ?
CS 622/215 Databases
Winter 14, Week 6
*
7
*
Entity Types
- Entity type: employee is entity type
- Alice, Bill are entities.
- Similar to type and var in programming languages
- Entity type represented by
- We will often say entity instead of entity type
- What are the entities in the company database?
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Attributes
- Attributes are properties used to describe an entity.
- EMPLOYEE entity type will have a Name, SSN, Address, Sex etc.
- Notation:
- A specific entity will have a value for each of its attributes. Eg: specific employee
- Name='John Smith’
- SSN='123456789 ’
- Address ='731 Broad, Houston, TX ’
- Sex='M'
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Key
- Key uniquely identifies which entity of that entity type we are talking about.
- Eg: SSN for employees
- Notation: by underlining
- What are the keys for the other entities.
- Notation different from relational schemas
- In ER diagrams we underline all keys, not only PK
- No notion of PK in ER diagrams
- all keys equal
- Show composite key by underlining
- Confusing since 2 diff. keys have same notation
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Simple vs Composite Attributes
- Simple: Each entity has a single atomic value for the attribute:
- Not broken into further details. Eg ?
- Eg : salary
- Notation:
- Composite: The attribute may be composed of several components
- Is broken down into further details. Eg ?
- Eg : name
- Notation:
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Multi valued/Single-valued attribute
- Multi-valued: A single entity may have more than one value for that attribute. Eg ?
- Dept locations : one Dept , diff. locations
- Notation:
- Single valued: Each entity can have a only a single value for the attribute. Eg ?
- BDate
- Notation:
- Is single valued same as a key ?
- No: Bdate is not key. Why ?
- Two employee can have same Bdate
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Stored vs derived attributes
- Stored: Whose value has been entered i.e. they are not being derived from other attributes.
- Eg: BDate
- Notation:
- Derived: Whose value can be derived from the value of another attribute.
- Eg: Age. Derived from which attribute ?
- Birthdate
- Notation:
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Relationships
- Relationship: connection between two entities
- Alice works for Accounting
- Bob works for Marketing
- David works for Accounting
- Notation:
- Two entitites can participate in more than one relationship. Eg ?
- WORKS_FOR, MANAGES
- What are the relationships in Elmasri company database ?
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Recursive Relationships
- Recursive Relationship: Relationship between two entities of the same type. Eg ?
- SUPERVISION relationships between EMPLOYEE (in role of supervisor or boss) and (another) EMPLOYEE (in role of subordinate or worker).
- In ER diagram, need to display role names to distinguish participations.
CS 622/215 Databases
Winter 14, Week 6
*
7
*
Cardinality
- Capture information about max # relationships (of that type) that a particular entity can take part in? If 2 entity types in relationship, can be
- one-to-many 1:N (or N:1)
- one-to-one 1:1
- many-to-many M:N
- Derived from knowledge of mini-world constraints
- Has to be based on the semantics of the data
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Cardinality
- Eg of 1:N : WORKS_FOR
- One employee can only work in one dept
- One department can have many employees
- Notation: Put numbers on line for relationship
- WORKS_FOR :Put 1 on Dept side , N on Emp side
- Eg of 1:1: MANAGES
- One employee can manage at most one work dept
- One department can have at most one manager
- Eg of M:N :WORKS_ON
- One employee can work on many projects
- One project can have many employees working on it
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Partial/Total Participation
- What is min # of relationships (of that relationship type) that entity has to take part in.
- Partial participation: can be zero
- OK for an entity to be not involved in this relationship
- Notation: single or thin line
- Total participation: one or more
- Each entity has to take part in at least one such relationship
- Notation: double or fat line
CS 622/215 Databases
Winter 14, Week 6
*
7
*
Partial/Total Participation
- Could be:
- Total on both sides.
- Partial on both sides.
- Partial on one side, total on the other.
- Employees manage Department ?
- Partial – total. Why ?
- Not every employee manages a Dept.
- Partial from E
- Every Dept. has a manager
- Total from D
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Partial/Total Participation
- Employees work for Dept ?
- Total on both sides
- Every Emp has to work for a Dept.
- Every Dept has to have an Emp
- Not possible for Dept to exist without Emps
- Supervision?
- Partial – partial (what about cardinality?)
- Not every employee is a supervisor
- Not every employee has a supervisor
- Eg: WORKS_FOR, WORKS_ON, CONTROLS
CS 622/215 Databases
Winter 14, Week 6
*
7
*
Attributes of Relationship
- Eg: HoursPerWeek of WORKS_ON
- # hours per week EMP works on a PROJECT.
- Notation
- Could this be moved to Emp or Project ?
- No : many-many relationship
- MGR_START_DATE: could this be moved to Emp or Dept ?
- Yes, one-one
- But better to show as attribute of MANAGES relationship since it is really telling us about that
CS 622/215 Databases
Winter 14, Week 6
*
7
*
Weak Entities
- Want to keep track of dependents. What is the key ?
- Can name be a key for dependents table?
- No : 2 employees could have a son called John
- Not regular entity: can’t identify since no key
- Has to be a weak entity : Notation
- How to distinguish one Dependent from another ?
- What info do we need to figure out which Dependent we are talking about ?
- Name can be a partial key:
- if we know Emp SSN and dependent name, can figure out which dependent we are talking about.
- Key will be {ESSN, Dependent Name}
CS 622/215 Databases
Winter 14, Week 6
*
7
*
Weak Entities
- A weak entity must participate in an identifying relationship with a regular (owner) entity.
- Notation
- Weak entity DEPENDENT
- Identifying relationship DEPENDENT_OF
- Regular entity EMPLOYEE
- From the side of the weak entity : can this be many (N:N or N:1) ?
- No: has to be 1:N or 1:1. Can be partial?
- No: from weak entity side, has to be total
CS 622/215 Databases
Winter 14, Week 6
*
7
*
SUMMARY OF ER-DIAGRAM
NOTATION FOR ER SCHEMAS
Meaning
ENTITY TYPE
WEAK ENTITY TYPE
RELATIONSHIP TYPE
IDENTIFYING RELATIONSHIP TYPE
ATTRIBUTE
KEY ATTRIBUTE
MULTIVALUED ATTRIBUTE
COMPOSITE ATTRIBUTE
DERIVED ATTRIBUTE
TOTAL PARTICIPATION OF E2 IN R
CARDINALITY RATIO 1:N FOR E1:E2 IN R
Symbol
R
E2
E1
R
E2
N
1
*
The ER conceptual schema diagram for the COMPANY database.
- On next slide we will see another alternative notion (min, max) from text book
- Use if you like, but ONLY IF YOU UNDERSTAND IT !
*
FIGURE 7.15 The ER diagram for COMPANY with (min, max) notation for participation and cardinality
*
Cardinality vs participation
- Cardinality : max # relationships for entity
- Participation : min # relationships for entity
- Independent of each other. Can be:
- Partial and 1. Eg: Manages from Emp
- Partial and N. Eg: Supervises from supervisor:
- 123 may not be supervisor
- 234 may be a supervisor for many supervisees
- Total and 1. Eg: Manages from Dept.
- Total and N. Eg: Employee works for Dept
- Dept has to have at least one Emp, could have many
CS 622/215 Databases
Winter 14, Week 6
*
7
*
SQL REVIEW: Find age of youngest sailor with age > 18, for each rating with at least 2 sailors, of any age
HAVING (SELECT COUNT (*)
FROM Sailors S2
WHERE S.rating=S2.rating) > 1;
- Compare this with the query where we considered only ratings with 2 sailors over 18
SELECT S.rating, MIN (S.age)
FROM Sailors S
WHERE S.age > 18
GROUP BY S.rating
CS 622/215 Databases
Winter 14, Week 6
*
*
Ternary relationships
- Degree of relationship: # participating entitities.
- Binary Relationship: Relationship types of degree 2
- Eg: MANAGES
- Ternary Relationship: Relationship types of degree 3
- Eg: Elmasri SUPPLY relationship from next slide.
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Elmasri FIGURE 7.17
Ternary relationship types: SUPPLY relationship.
*
Ternary relationships
- Can we replace a ternary relationship with two binary relationships?
- Supp-Proj, Supp-Part
- No : will lose information. Eg:
- S1 supplies to Proj1
- S1 supplies Part1
- Can we conclude S1 supplies Part1 to Proj1 ?
- No : possible that
- S1 supplies Part2 to Proj1
- S1 supplies Part1 to Proj2
CS 622/215 Databases
Winter 14, Week 6
*
10
*
Ternary relationships
- Lossy decomposition: can’t recover original table.
- Can replace with three binary relationships?
- supp-proj, supp-part, part-proj
- No. Counterexample ?
CS 622/215 Databases
Winter 14, Week 6
*
7
*
Ternary relationships
- Suppose we saw
- S1 supplies to Proj1
- S1 supplies Part1
- Part1 supplied to Proj1
- Can we conclude S1 supplies Part1 to Proj1 ?
- No
- S1 supplies Part2 to Proj1
- S1 supplies Part1 to Proj2
- S2 supplies Part1 to Proj1
CS 622/215 Databases
Winter 14, Week 6
*
7
*
RG Eg: Ternary Relationships
age
pname
Dependents
Covers
- Does this look OK ?
- Policy is for E or D ?
- Suppose one table (E,P,D):
- (E1,P1,D1), (E1,P1,D2), (E1,P1,D3), …
- P1 is for E1, repeated for each D
- How to fix ?
- Two binary relationships – how ?
CS 622/215 Databases
Winter 14, Week 6
*
7
*
RG Eg: Ternary Relationships: can sometimes be better with 2 binary
- 2 binary relationships, 2 tables
- (E,P):
- (P,D):
- No repetition
- RG: different notation for cardinality
- Uses arrows
age
pname
Dependents
Covers
age
pname
Dependents
Purchaser
Bad design
Better design
CS 622/215 Databases
Winter 14, Week 6
*
7
*
Points and Common Mistakes with ER diagrams
- May not be able to capture all constraints.
- Eg: Emp in Acct dept have > 40k salary
- Have entity types and not actual entities
- Eg: EMPLOYEE, not Mike
- Don’t represent the thing which the whole database is about as an entity
- Eg: COMPANY as an entity
CS 622/215 Databases
Winter 14, Week 6
*
7
*
Entity vs. Attribute
- Should address be attribute of E or an entity
- (connected to Employees by a relationship)?
- Depends upon use we want to make of address information, and the semantics of the data:
- Eg: better as separate entity if:
- several addresses per employee
- If details of address (city, street, etc.) are important
- In a different database, could be a separate entity
CS 622/215 Databases
Winter 14, Week 6
*
7
*
Attribute vs relationship
- Eg: suppose we removed MANAGES relationship and had a MANAGER attribute for DEPT. Good idea ?
- No: managers are also employees
- If we have as attribute, then we are not making the connection that managers have to be employees
- Eg: students and advisors ?
- If keeping track of professors, then relationship
- Else possibly attribute
CS 622/215 Databases
Winter 14, Week 6
*
7
*
Redundancy
- Question: Any redundant info?
- Eg: if we also had Manager as an attribute of DEPARTMENT.
- Why is this redundant?
- Already have this info through the MANAGES relationship
- Eg: suppose have LOCATION entity connected via a relationship to PROJECT. Problem?
- Not OK to still have location information as attribute and via a relationship
CS 622/215 Databases
Winter 14, Week 6
*
7
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
10
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6
CS 622/215 Databases
CS 622/215 Databases
Winter 14, Week 6
*
7
Winter 14, Week 6