Answers






Answers

16.1

A primary key cannot and may not contain NULL values. SQL requires that, for each column belonging to a primary key, NOT NULL must be defined.

16.2

For each table, only one primary key can be defined, but it is not mandatory.

16.3

CREATE   TABLE MATCHES
        (MATCHNO     INTEGER NOT NULL,
         TEAMNO      INTEGER NOT NULL,
         PLAYERNO    INTEGER NOT NULL,
         WON         INTEGER NOT NULL,
         LOST        INTEGER NOT NULL,
         PRIMARY KEY  (MATCHNO))

or

CREATE   TABLE MATCHES
        (MATCHNO     INTEGER NOT NULL PRIMARY KEY,
         TEAMNO      INTEGER NOT NULL,
         PLAYERNO    INTEGER NOT NULL,
         WON         INTEGER NOT NULL,
         LOST        INTEGER NOT NULL)

16.4

Column C4 in the definition of the primary key does not exist.

Column C1 is defined as the primary key twice; this is not permitted.

The first alternate key on the column C3 is a subset of the second on the columns C2 and C3.

16.5

Foreign keys are defined to force SQL to check that no incorrect data can be entered in the tables.

16.6

The following updates are no longer permitted:

Deleting a player from the PLAYERS table is now permitted only if that player has played no matches.

Updating a player number in the PLAYERS table is possible only if that player has played no matches.

Deleting a team from the TEAMS table is now permitted only if no matches have been played by that team.

Updating a team number in the TEAMS table is possible only if no matches have been played by that team.

No restrictions are imposed by the foreign keys on inserting new players into the PLAYERS table.

No restrictions are imposed by the foreign keys on inserting new teams into the TEAMS table.

No restrictions are imposed by the foreign keys on deleting matches from the MATCHES table.

Updating a player number in the MATCHES table is permitted only if the new player number already occurs in the PLAYERS table.

Updating a team number in the MATCHES table is permitted only if the new team number already occurs in the TEAMS table.

Inserting new matches in the MATCHES table is permitted only if the new player number already occurs in the PLAYERS table and the new team number already occurs in the TEAMS table.

16.7

If the referencing table and the referenced table are the same for the same foreign key, we call this self-referential integrity.

16.8

Yes.

16.9

This is the same as the specification of ON UPDATE RESTRICT and ON DELETE RESTRICT.

16.10

The following updates are no longer permitted:

Deleting a player from the PLAYERS table is now permitted only if that player has played no matches: ON UPDATE RESTRICT.

Updating a player number in the PLAYERS table is allowed: ON DELETE CASCADE.

Deleting a team from the TEAMS table is not permitted: ON DELETE RESTRICT.

Updating a team number in the TEAMS table is allowed: ON UPDATE CASCADE.

No restrictions are imposed by the foreign keys on inserting new players into the PLAYERS table.

No restrictions are imposed by the foreign keys on inserting new teams into the TEAMS table.

No restrictions are imposed by the foreign keys on deleting matches from the MATCHES table.

Updating a player number in the MATCHES table is permitted only if the new player number already occurs in the PLAYERS table.

Updating a team number in the MATCHES table is permitted only if the new team number already occurs in the TEAMS table.

Inserting new matches in the MATCHES table is permitted only if the new player number already occurs in the PLAYERS table and the new team number already occurs in the TEAMS table.

16.11

CHECK(AMOUNT > 0)

16.12

CHECK(WON > LOST AND WON + LOST < 6)

16.13

CHECK(BEGIN_DATE BETWEEN '1990-01-01' AND
 COALESCE(END_DATE, '9999-01-01')



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows