Transcript 6_views_er - Homework Market

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
1
Figures 3.59-3.60: SQL Query to Create New Table
2
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
3
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;
4
Figure 2.37: Make-Table Query
5
Figure 2.39: Make-Table Query (con’t.)
6
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
7
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;
8
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
9
Figures 4.3 - 4.4: Access Query Design of View
10
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);
11
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);
12
•
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
13
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’ ;
14
What kinds of view can be built?
•
•
•
•
•
Subset of rows
Subset of columns
Contain derived attributes
Renaming of columns
Can be from multiple tables
15
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’;
16
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;
17
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
18
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.
19
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.
20
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.
21
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 ?
22
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?
23
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'
24
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
25
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:
26
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
27
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:
28
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 ?
29
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.
30
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
31
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
32
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
33
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
34
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
35
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
36
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}
37
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
38
SUMMARY OF ER-DIAGRAM
NOTATION FOR ER SCHEMAS
Symbol
Meaning
ENTITY TYPE
WEAK ENTITY TYPE
RELATIONSHIP TYPE
IDENTIFYING RELATIONSHIP TYPE
ATTRIBUTE
KEY ATTRIBUTE
MULTIVALUED ATTRIBUTE
COMPOSITE ATTRIBUTE
DERIVED ATTRIBUTE
E1
E1
E2
R
1
R
N
E2
TOTAL PARTICIPATION OF E2 IN R
CARDINALITY RATIO 1:N FOR E1:E2 IN R
39
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 !
40
FIGURE 7.15 The ER diagram for COMPANY with (min,
max) notation for participation and cardinality
41
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
42
SQL REVIEW: Find age of youngest sailor with
age > 18, for each rating with at least 2 sailors, of
any age
SELECT S.rating, MIN (S.age)
FROM Sailors S
WHERE S.age > 18
GROUP BY S.rating
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
43
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.
44
Elmasri FIGURE 7.17
Ternary relationship types: SUPPLY relationship.
45
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
46
Ternary relationships
• Lossy decomposition: can’t recover original
table.
• Can replace with three binary relationships?
– supp-proj, supp-part, part-proj
• No. Counterexample ?
47
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
48
RG Eg: Ternary Relationships
• Does this
look OK ?
name
ssn
Employees
• Policy is for
E or D ?
pname
lot
age
Dependents
Covers
Policies
policyid
cost
• 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 ?
49
RG Eg: Ternary Relationships: can
sometimes be better with 2 binary
• 2 binary
relationships,
2 tables
name
ssn
Employees
• No repetition
• RG: different
notation for
cardinality
– Uses arrows
age
Dependents
Covers
Bad design
– (E,P):
– (P,D):
pname
lot
Policies
policyid
cost
name
pname
ssn
lot
age
Dependents
Employees
Purchaser
Beneficiary
Better design
policyid
Policies
cost
50
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
51
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
52
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
53
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
54