Example SQL Tables

1

• Example SQL Tables
• Manager (Manager_No, Manger_Name)
• Player(Player_No, Name, Goals_Scored, Manager_No)
• (i) Write the SQL statement to list all the
  players in descending order of goals - all
  players who have scored at least 1 goal
• Select from Player where goals_scored gt1 order
  by goals_scored desc
• (ii) Write the SQL to list the names of the
  managers who have players who have scored at
  least 5 goals.
• Select distinct manager_name from manager inner
  join player on manager.manager_no
  player.manager_no where
• goals_scored gt 5

2

• (iii) Show, using an outline drawing, how the
  first query (select all players who scored more
  than 5 goals) could be accomplished using QBE
  within ACCESS
• __________
• Players
• ---------------
• Player No
• Name etc...
• _________
• __________________________________________________
  ___
• Field Player No Name Goals
  Scored Manager No
• Table Players Players Players
  Players
• Sort Desc
• Show X X X
  X
• Criteria
  gt5
• __________________________________________________
  ___

3

• Data Independence (See chapter 2.1.5 - Connolly)
• major objective of the three-level architecture
  is to provide data independence which means that
  the upper levels are unaffected by changes to the
  lower levels. There are two kinds of data
  independence - Logical and Physical.

• Logical Data Independence
• Refers to immunity of external schemas to changes
  in conceptual schema.
• Conceptual schema changes e.g. addition/removal
  of entities.
• Should not require changes to external schema or
  rewrites of application programs.
• Physical Data Independence
• Refers to immunity of conceptual schema to
  changes in the internal schema.
• Internal schema changes e.g. using different file
  organizations, storage structures/devices.
• Should not require change to conceptual or
  external schemas.

4

• ANSI/SPARC 3 level architecture
• What is represented at each of the 3
  schema/levels in the ANSI-SPARC Model?

• External Level
• Users' view of the database. Describes that part
  of database that is relevant to a particular
  user.
• Conceptual Level
• Community view of the database. Describes what
  data is stored in database and relationships
  among the data.
• Internal Level
• Physical representation of the
• database on the computer.
• Describes how the
• data is stored in the database.

5

• Concurrency/Deadlocking and Wait-For-Graphs

• Process of managing simultaneous operations on
  the database without having them interfere with
  one another.
• Prevents interference when two or more users are
  accessing database simultaneously and at least
  one is updating data. Although two transactions
  may be correct in themselves, interleaving of
  operations may produce an incorrect result.
• The three classic examples of potential problems
  caused by concurrency are the
• Lost update problem
• Uncommitted dependency problem
• Inconsistent analysis problem.

Understand these three problems (see chapter 17
of Connolly)
6

• Deadlock

• An impasse that may result when two (or more)
  transactions are each waiting for locks held by
  the other to be released. Only one way to break
  deadlock abort one or more of the transactions.

Deadlock should be transparent to user, so DBMS
should restart transaction(s). Two general
techniques for handling deadlock Deadlock
prevention. Deadlock detection and recovery.
7
Deadlock Detection and Prevention
(Wait-For-Graphs)

• DBMS allows deadlock to occur but recognizes it
  and breaks it.
• Usually handled by construction of wait-for graph
  (WFG) showing transaction dependencies
• Create a node for each transaction.
• Create edge Ti -gt Tj, if Ti waiting to lock item
  locked by Tj.
• Deadlock exists if and only if WFG contains
  cycle.
• WFG is created at regular intervals.

8
Transaction Support - ACID Properties - 2 phase
locking

• Can have one of two outcomes
• Success - transaction commits and database
  reaches a new consistent state.
• Failure - transaction aborts, and database must
  be restored to consistent state before it
  started.
• Such a transaction is rolled back or undone.
• Committed transaction cannot be aborted.
• Aborted transaction that is rolled back can be
  restarted later.
• Four basic (ACID) properties of a transaction
  are
• Atomicity 'All or nothing' property.
• Consistency Must transform database from one
  consistent state to another.
• Isolation Partial effects of incomplete
  transactions should not be visible to other
  transactions.
• Durability Effects of a committed transaction
  are permanent and must not be lost because of
  later failure.

9
Two-Phase Locking - What actually happens?

• Transaction follows 2PL protocol if all locking
  operations precede first unlock operation in the
  transaction.
• Two phases for transaction
• Growing phase - acquires all locks but cannot
  release any locks.
• Shrinking phase - releases locks but cannot
  acquire any new locks.

• Using 2-PL to solve Uncommitted dependency
  problem

• Using 2-PL to solve Lost Update problem

10
Views Their influence on Security and Integrity
(chapter 16)
View A view is the dynamic result of one or more
relational operations operating on the base
relations to produce another relation. A view is
a virtual relation that does not actually exist
in the database, but is produced upon request by
a particular user, at the time of request.
The view mechanism provides a powerful and
flexible security mechanism by hiding parts of
the database from certain users. The user is not
aware of the existence of any attributes or rows
that are missing from the view. A view can be
defined over several tables with a user being
granted the appropriate privilege to use it, but
not to use the base tables. In this way, using
a view is more restrictive than simply having
certain privileges granted to a user on the base
tables.