COP4540 Database Management System Midterm Review

1
COP4540 Database Management SystemMidterm Review

• Reviewed by
• Fernando Farfán

2
AGENDA

• Ch1. Overview of DBMSs
• Ch2. Database Design
• Ch3. Relational Model
• Ch4. Relational Algebra
• Ch19. Normal Forms
• Ch5. SQL

3
AGENDA

• Ch1. Overview of DBMSs
• Ch2. Database Design
• Ch3. Relational Model
• Ch4. Relational Algebra
• Ch19. Normal Forms
• Ch5. SQL

4
CH1 EXERCISES

• 1.2. What is logical data independence and why is
  it important?
• Users are shielded from changes in the logical
  structure of the data.
• i.e., changes in the choice of relations to be
  stored.
• Changes are transparent to the application
  program.

5
CH1 EXERCISES

• 1.4. Explain the difference between external,
  internal, and conceptual schemas. How are these
  different schema layers related to the concepts
  of logical and physical data independence?
• External schemas Allow data access to be
  customized (and authorized) at the level of
  individual users or groups of users.
• Conceptual (logical) schemas Describe all the
  data that is actually stored in the database.
  While there are several views for a given
  database, there is exactly one conceptual schema
  to all users.
• Internal (physical) schemas Summarize how the
  relations described in the conceptual schema are
  actually stored on disk (or other physical media).

6
CH1 EXERCISES

• 1.6. Scrooge McNugget wants to store information
  (names, addresses, descriptions of embarrassing
  moments, etc.) Indicate which of the following
  DBMS features he should pay for in each case,
  also indicate why he should (or should not) pay
  for that feature in the system he buys.
• Security facility Necessary. Scrooge does not
  plan to share his list with anyone else. Even
  though he is running it on his stand-alone PC, a
  rival could break in and attempt to query his
  database. The databases security features would
  foil the intruder.
• Concurrency control Not needed. Only he uses the
  database.
• Crash recovery Needed. Scrooge would not want to
  lose his data if the power was interrupted while
  he was using the system.
• View mechanism Needed. Scrooge could use this to
  develop custom screens that he could
  conveniently bring up without writing long
  queries repeatedly.
• Query language Necessary. Scrooge must be able
  to analyze the data. In particular, the query
  language is also used to define views.

7
AGENDA

• Ch1. Overview of DBMSs
• Ch2. Database Design
• Ch3. Relational Model
• Ch4. Relational Algebra
• Ch19. Normal Forms
• Ch5. SQL

8
CH2 EXERCISES

• 2.2. A university database contains information
  about professors (id. by SSN) and courses (id.
  by courseid). Professors teach courses each of
  the following situations concerns the Teaches
  relationship set. For each situation, draw an ER
  diagram that describes it (assuming no further
  constraints hold).
• Professors can teach the same course in several
  semesters, and each offering must be recorded.

9
CH2 EXERCISES

• 2.2. CONT
• Professors can teach the same course in several
  semesters, and only the most recent such offering
  needs to be recorded. (Assume this condition
  applies in all subsequent questions.)

10
CH2 EXERCISES

• 2.2. CONT
• Every professor must teach some course.

11
CH2 EXERCISES

• 2.2. CONT
• Every professor teaches exactly one course (no
  more, no less).

12
CH2 EXERCISES

• 2.2. CONT
• Every professor teaches exactly one course (no
  more, no less), and every course must be taught
  by some professor.

13
CH2 EXERCISES

• 2.2. CONT
• Certain courses can be taught by a team of
  professors jointly, but it is possible that no
  one professor in a team can teach the course.
  Model this situation, introducing additional
  entity sets and relationship sets if necessary.

14
CH2 EXERCISES

• 2.4. A company database needs to store
  information about employees (ssn, salary, phone),
  departments (dno, dname, budget), and children of
  employees (name, age)Draw an ER diagram that
  captures this information.

15
CH2 EXERCISES

• 2.8. Galleries database to keep information
  about artists (unique names, birthplaces, age,
  and style of art), pieces of artwork (artist,
  year it was made, unique title, type of art
  (e.g., painting, lithograph), and price) Draw
  the ER diagram for the database.

16
AGENDA

• Ch1. Overview of DBMSs
• Ch2. Database Design
• Ch3. Relational Model
• Ch4. Relational Algebra
• Ch19. Normal Forms
• Ch5. SQL

17
CH3 EXERCISES

• 3.2. How many distinct tuples are in a relation
  instance with cardinality 22?
• Since a relation is formally defined as a set of
  tuples, if the cardinality is 22 (i.e., there are
  22 tuples), there must be 22 distinct tuples.

18
CH3 EXERCISES

• 3.4. What is the difference between a candidate
  key and the primary key for a given relation?
  What is a superkey?
• The primary key is the key selected by the DBA
  from among the group of candidate keys, all of
  which uniquely identify a tuple. A superkey is a
  set of attributes that contains a key.

19
CH3 EXERCISES

• 3.8. Answer each of the following questions
  briefly. The questions are based on the following
  relational schema Emp(eid integer, ename
  string, age integer, salary real) Works(eid
  integer, did integer, pcttime
  integer) Dept(did integer, dname string,
  budget real, managerid integer)
• 1. Give an example of a foreign key constraint
  that involves the Dept relation. What are the
  options for enforcing this constraint when a user
  attempts to delete a Dept tuple?
• Consider the following example. It is natural to
  require that the did field of Works should be a
  foreign key, and refer to Dept. CREATE TABLE
  Works ( eid INTEGER NOT NULL , did INTEGER NOT
  NULL , pcttime INTEGER, PRIMARY KEY (eid,
  did), UNIQUE (eid), FOREIGN KEY (did)
  REFERENCES Dept )

20
CH3 EXERCISES

• 3.8. 2. Write the SQL statements required to
  create the preceding relations, including
  appropriate versions of all primary and foreign
  key integrity constraints.

21
CH3 EXERCISES

• 3.8. 3. Define the Dept relation in SQL so that
  every department is guaranteed to have a manager.
• CREATE TABLE Dept ( did INTEGER, budget
  REAL, managerid INTEGER NOT NULL , PRIMARY
  KEY (did), FOREIGN KEY (managerid) REFERENCES
  Emp)

22
CH3 EXERCISES

• 3.8.4. Write an SQL statement to add John Doe as
  an employee with eid 101, age 32 and salary
  15, 000.
• INSERT INTO Emp (eid, ename, age, salary)VALUES
  (101, John Doe, 32, 15000)

23
CH3 EXERCISES

• 3.8.5. Write an SQL statement to give every
  employee a 10 percent raise.
• UPDATE Emp ESET E.salary E.salary 1.10

24
CH3 EXERCISES

• 3.8.6. Write an SQL statement to delete the Toy
  department. Given the referential integrity
  constraints you chose for this schema, explain
  what happens when this statement is executed.
• DELETEFROM Dept DWHERE D.dname Toy

25
CH3 EXERCISES

• 3.18. Write SQL statements to create the
  corresponding relations to the ER diagram you
  designed for Exercise 2.8. If your translation
  cannot capture any constraints in the ER diagram,
  explain why.
• CREATE TABLE Artwork Paints ( title
  CHAR(20), artist name CHAR(20), type
  CHAR(20), price INTEGER, year INTEGER, PRIMARY
  KEY (title), FOREIGN KEY (artist
  name) References Artist)

26
AGENDA

• Ch1. Overview of DBMSs
• Ch2. Database Design
• Ch3. Relational Model
• Ch4. Relational Algebra
• Ch19. Normal Forms
• Ch5. SQL

27
CH4 EXERCISES

• 4.2. Given two relations R1 and R2, where R1
  contains N1 tuples, R2 contains N2 tuples, and N2
  gt N1 gt 0, give the min and max possible sizes for
  the resulting relational algebra expressions(1)
  R1UR2, (2) R1nR2, (3) R1-R2, (4) R1R2, (5)
  sa5(R1), (6) pa(R1), and (7) R1/R2

28
CH4 EXERCISES

• 4.4. Consider the Supplier-Parts-Catalog schema
  from the previous question. State what the
  following queries compute

29
CH4 EXERCISES

• 4.4. Consider the Supplier-Parts-Catalog schema
  from the previous question. State what the
  following queries compute

30
CH4 EXERCISES

• 4.6. What is relational completeness? If a query
  language is relationally complete, can you write
  any desired query in that language?
• Relational completeness means that a query
  language can express all the queries that can be
  expressed in relational algebra. It does not mean
  that the language can express any desired query.

31
AGENDA

• Ch1. Overview of DBMSs
• Ch2. Database Design
• Ch3. Relational Model
• Ch4. Relational Algebra
• Ch19. Normal Forms
• Ch5. SQL

32
CH19 EXERCISES

• 19.2. Consider a relation R with five attributes
  ABCDE. You are given the following dependencies
  A ? B, BC ? E, and ED ? A.
• List all keys for R.
• CDE, ACD, BCD
• Is R in 3NF?
• R is in 3NF because B, E and A are all parts of
  keys.
• Is R in BCNF?
• R is not in BCNF because none of A, BC and ED
  contain a key.

33
CH19 EXERCISES

• 19.4. Assume that you are given a relation with
  attributes ABCD.
• Assume that no record has NULL values. Write an
  SQL query that checks whether the functional
  dependency A ? B holds.
• SELECT COUNT ()FROM R AS R1, R AS R2WHERE
  (R1.B ! R2.B) AND (R1.A R2.A)
• Assume again that no record has NULL values.
  Write an SQL assertion that enforces the
  functional dependency A ? B.
• CREATE ASSERTION ADeterminesB CHECK ((SELECT
  COUNT () FROM R AS R1, R AS R2 WHERE (R1.B
  ! R2.B) AND (R1.A R2.A)) 0)

34
CH19 EXERCISES

• 19.4.
• Let us now assume that records could have NULL
  values. Repeat the previous two questions under
  this assumption.
• SELECT COUNT ()FROM R AS R1, R AS R2WHERE
  ((R1.B ! R2.B) AND (R1.A R2.A)) OR ((R1.B is
  NULL) AND (R2.B is NOT NULL) AND (R1.A
  R2.A))CREATE ASSERTION ADeterminesBNullCHECK
  ((SELECT COUNT () FROM R AS R1, R AS R2 WHERE
  ((R1.B ! R2.B) AND (R1.A R2.A))) OR ((R1.B
  is NULL) AND (R2.B is NOT NULL) AND (R1.A
  R2.A)) 0)

35
AGENDA

• Ch1. Overview of DBMSs
• Ch2. Database Design
• Ch3. Relational Model
• Ch4. Relational Algebra
• Ch19. Normal Forms
• Ch5. SQL

36
CH5 EXERCISES

• 5.2. Consider the following schema Suppliers(si
  d integer, sname string, address
  string) Parts(pid integer, pname string,
  color string) Catalog(sid integer, pid
  integer, cost real)The Catalog relation lists
  the prices charged for parts by Suppliers. Write
  the following queries in SQL
• 1. Find the pnames of parts for which there is
  some supplier.
• SELECT DISTINCT P.pnameFROM Parts P, Catalog
  CWHERE P.pid C.pid
• 5. Find the sids of suppliers who charge more for
  some part than the average cost of that part
  (averaged over all the suppliers who supply that
  part).
• SELECT DISTINCT C.sidFROM Catalog CWHERE C.cost
  gt ( SELECT AVG (C1.cost) FROM Catalog
  C1 WHERE C1.pid C.pid )

37
CH5 EXERCISES

• 5.4. Consider the following relational schema. An
  employee can work in more than one department
  the pct time field of the Works relation shows
  the percentage of time that a given employee
  works in a given department. Emp(eid integer,
  ename string, age integer, salary
  real) Works(eid integer, did integer, pct
  time integer) Dept(did integer, dname string,
  budget real, managerid integer)Write the
  following queries in SQL
• Print the names and ages of each employee who
  works in both the Hardware department and the
  Software department.
• SELECT E.ename, E.ageFROM Emp E, Works W1, Works
  W2, Dept D1, Dept D2WHERE E.eid W1.eid AND
  W1.did D1.did AND D1.dname Hardware AND
  E.eid W2.eid AND W2.did D2.did AND D2.dname
  Software
• 3. Print the name of each employee whose salary
  exceeds the budget of all of the departments that
  he or she works in.
• SELECT E.enameFROM Emp EWHERE E.salary gt ALL
  (SELECT D.budget FROM Dept D, Works W WHERE
  E.eid W.eid AND D.did W.did)

38
CH5 EXERCISES

• 5.6. Answer the following questions
• Explain the term impedance mismatch in the
  context of embedding SQL commands in a host
  language such as C.
• The impedance mismatch between SQL and many host
  languages such as C or Java arises because SQL
  operates on sets, and there is no clean
  abstraction for sets in a host language.
• How can the value of a host language variable be
  passed to an embedded SQL command?
• Variables in a host language must first be
  declared between EXEC SQL BEGIN DECLARE SECTION
  and EXEC SQL END DECLARE SECTION commands. Once
  variables are declared, they can be used by
  prefixing the variable name with a colon ().
• Explain the WHENEVER commands use in error and
  exception handling.
• The WHENEVER command in SQL allows for easy error
  and exception checking after an embedded SQL
  statement is executed. WHENEVER checks the value
  of SQLSTATE for a specified error. If an error
  has occurred, the WHENEVER command will transfer
  control to a specified section of error handling
  code.
• Explain the need for cursors.
• Cursors provide a mechanism for retrieving rows
  one at a time from a relation. A cursor is the
  abstraction that is missing from most host
  languages, causing an impedance mismatch.
• Give an example of a situation that calls for the
  use of embedded SQL that is, interactive use of
  SQL commands is not enough, and some host
  language capabilities are needed.
• One example where SQL is insufficient is when the
  variance of some data is needed. Although SQL has
  many useful aggregate functions such as COUNT and
  AVG , these are not powerful enough to compute
  variances. In this case, users can use embedded
  SQL to perform more involved aggregates.

39
GOOD LUCK!!

• Fernando Farfán
• ffarf001_at_cis.fiu.edu
• ECS256
• Ext. 2486