Week 6

Question 1

What does date store?

Answer 1

A 4 digit year, month and date

Question 2

What does time store?

Answer 2

Time of day in hours, minutes and seconds

Question 3

What does datetime store?

Answer 3

The date plus the time of day

Question 4

What is the difference between datetime and timestamp?

Answer 4

Timestamp has timezones

Question 5

Why would you choose to create a domain over a type?

Answer 5

Easier to have constraints and default values compared to types

Question 6

Why would you create indexes?

Answer 6

Helps to speed up searches

Question 7

What do indexes slow down?

Answer 7

Insert, update and delete operations

Question 8

What is the syntax for creating an index?

Answer 8

CREATE INDEX index_name ON table_name (A1, A2,…)

Question 9

What is the motivation for creation of views?

Answer 9

So that not all users have to see the entire logical model of a database

Question 10

Describe a view

Answer 10

A “virtual relation” that conceptually contains the results of a query, but in reality the query is re-executed each time the view is called

Question 11

What is the syntax for creating a view?

Answer 11

create view v as

Question 12

Why do most SQL implemenations only let you update simple views?

Answer 12

Updates can’t be translated uniquely and we end up with ambiguity about what other updates need to take place in the database

Can allow for items to go into view where the where clause for original view doesn’t hold

Question 13

What conditions have to hold for a view to be updatable?

Answer 13

<ul>
<li>The from only has one table</li>
<li>The select only has attributes names of the relation, no expressions, aggregates or distinct specifications</li>
<li>Any attribute not listed in the select clause can be set to null</li>
<li>The query does not have a group by or having clause</li>
</ul>

Question 14

What is a materialized view?

Answer 14

Computes and stores results at the time of creation

Question 15

What are the 5 forms of authorisation on the databse?

Answer 15

<ol>
<li>Select</li>
<li>Insert</li>
<li>Update</li>
<li>Delete</li>
<li>Execute</li>
</ol>

Question 16

What are the 3 forms of authorisation on the database schema?

Answer 16

<ol>
<li>Create</li>
<li>Alter</li>
<li>Drop</li>
</ol>

Question 17

What does select authorisation give?

Answer 17

Allows reading but not modification of data

Question 18

What does insert authorisation give?

Answer 18

Allows insertion of new data but not modification of existing data

Question 19

What does update authorisation give?

Answer 19

Allows modification, but not insertion or deletion of data

Question 20

What does delete authorisation give?

Answer 20

Allows deletion of data

Question 21

What does execute authorisation give?

Answer 21

Allows execution of stored procedures

Question 22

What does create authorisation give?

Answer 22

Allows creation of tables/database

Question 23

What does alter authorisation give?

Answer 23

Allows addition or deletion of attributes in a relation

Question 24

What does drop authorisation give?

Answer 24

Allows deletion of tables/databases

Question 25

What is the syntax for the grant statement?

Answer 25

grant X on Y to Z

Question 26

What does the with grant option do?

Answer 26

Allow a user who is granted a privilege to pass the privilege on to other users

Question 27

Who is at the root of the authorisation graph?

Answer 27

The database administrator (DBA)

Question 28

What does restrict option mean for when a privilege is revoked?

Answer 28

Only the person stated privilege is revoked

Question 29

What does the cascade option mean for when a privilege is revoked?

Answer 29

Everything the person to be revoked has granted is revoked

Question 30

What is the Authorisation Grant Graph requirement?

Answer 30

All edges in an authorisation graph must be part of some path originating with the database administrator

Question 31

How would you grant select access at global level?

Answer 31

grant select on . to ‘user’@’%’

Question 32

What is the main limitation of SQL authorisation?

Answer 32

Cannot grant privileges at the row level within the database directly

Question 33

How can row level authorisation be provided to users?

Answer 33

<ul>
<li>Application program that accesses database</li>
<li>Views</li>
<li>Procedures</li>
</ul>

Question 34

What can be combined in order to ensure users can only access precisely the data that the user needs?

Answer 34

<ul>
<li>Relational level security</li>
<li>View level security</li>
</ul>

Question 35

How do you do power in SQL?

Answer 35

pow(x,y)

x to the power of y

Question 36

How many values can a function return in SQL?

Answer 36

One

Question 37

What is the minimum number of values a function can return?

Answer 37

One

Question 38

What is the minimum number of values a procedure can return?

Answer 38

Zero

Question 39

How are functions and procedures related?

Answer 39

A function can always be a procedure, a procedure can’t always be a function

Question 40

Where can procedures be invoked?

Answer 40

From an SQL procedure or from programs, but not within a select query

Question 41

What would you use if you want to return the execution of an entire query?

Answer 41

Procedure

Question 42

What is a trigger?

Answer 42

Statement that is executed automatically by the system as a side effect of a modification to the database

Question 43

What must be specified when designing a trigger?

Answer 43

<ul>
<li>Conditions under which the trigger is executed</li>
<li>Actions to be taken when the trigger executes</li>
</ul>

Question 44

What is the basic trigger syntax?

Answer 44

CREATE TRIGGER {BEFORE | AFTER} ON FOR EACH ROW

Question 45

What can the events be in a trigger?

Answer 45

<ul>
<li>Insert</li>
<li>Delete</li>
<li>Update</li>
</ul>

Question 46

What can the action be in a trigger?

Answer 46

<ul>
<li>Single SQL update/insert/delete statement</li>
<li>Multiple statements within the BEGIN .... END construct </li>
</ul>