DP Computer Science Questionbank

Construct the entity-relationship diagram (ERD) for this scenario.

Outline two reasons why databases are normalized.

Outline why the SALES_PERSON table is not in 1st Normal Form (1NF).

State the result from the following query:

SELECT Book_Title
FROM BOOK
WHERE Genre = "Non-fiction"
AND ISBN = '0-98124-612-2'

Construct a query to find the titles of the books published by “Orlando Crux”.

Describe two ways that data security in the school's database can be maintained.

Construct the 3rd Normal Form (3NF) of the unnormalized relation shown above.

Outline why it is necessary to ensure that referential integrity is maintained in databases.

Outline why a primary key may consist of more than one attribute.

Some data in the MRBS database is redundant.

Outline one problem caused by redundant data.

Identify two different types of relationships within databases.

Describe the nature of the data dictionary.

State what is meant by redundant data in databases.

Describe the steps in a query that will output the names of all students who earned the maximum mark on Exam_Two.

Explain why a compound key is used for the SCHOOL_VOLUNTEERS_TABLE relation.

Describe the steps in a query that will output the Student_IDs of all candidates who passed.

Identify three characteristics of the 1st Normal Form (1NF) which are evident in this relation.

Explain one issue that can be caused by redundant data in a database.

The following shows the normalized SCHOOL_VOLUNTEERS_TABLE relation:

SCHOOLS_TABLE
Code, School_Name, Address

VOLUNTEERS_TABLE
Code, Date, Num_Volunteers

Discuss whether these relations are in third normal form (3NF).

Construct the database in the 3rd normal form (3NF) using the notation

Greengrocer (Prod_ID, Prod_Name, Prod_Price, Supp_Name, Supp_Contact, Supp_Phone)You must show all your workings.

You must show all your workings.

Define the term database management system (DBMS).

Explain the problems that may arise for the following function performed on the given relation. In your answer, you should give an appropriate example: Inserting a new tuple with an item that is supplied by existing supplier.

Identify three characteristics of logical schema.

Outline the purpose of a data definition language (DDL).

Identify an appropriate data type for Prod_Price.

Construct the steps in a query to output the names of all products supplied by Veggy Co. with prices in the range from 4.00 to 10.00 inclusive.

Outline the redundant data in this relation.

Explain the problems that may arise for the following function performed on the given relation. In your answer, you should give an appropriate example: Deleting a tuple from the relation.

Explain the problems that may arise for the following function performed on the given relation. In your answer, you should give an appropriate example: Modifying a specific attribute value in the tuple.

Construct an entity-relationship-diagram that shows the relationships between the class, order and family.

Identify one reason why the teacher’s name has been split into two fields.

The DBA is considering using the email address as the primary key, but is concerned that many of the 250 000 educators who are registering for this online forum may have more than one email address. Users may create duplicate accounts, deliberately or accidently, by using different email addresses as usernames.

Explain the factors that would need to be considered in using a composite primary key instead of only using the email address.

Outline why data redundancy may be a problem in the LORRY table.

Identify the steps to create a query to find the names and telephone numbers of the drivers who drive lorries that have more than 60 cubic metres of trailer space.

Explain, using the example described in the scenario, why referential integrity is important in databases.

Identify two characteristics of a conceptual schema.

Explain why the use of data modelling is critical to the success of a database, such as the one used in this scenario.

Identify two characteristics that make a database unnormalized.

Normalize the database to 3NF. Use the same format as shown, ensuring that primary keys are clearly indicated by underlining them.

Outline two issues caused by storing redundant data.

Outline why the inclusion of a derived field will not affect the normalization of a database.

Construct the entity-relationship diagram (ERD) that shows the relationship between the individual, their driving license, and their vehicle(s).

Explain how the Billetmania database management system ensures that a seat is not booked by two people simultaneously.

Define the term record.

Identify the steps to create a query to find the vineyards and names of fruity wines where the quantity in stock is between 25 and 35 bottles.

Identify the steps to create a non-persistent derived field called TotalPrice, which would hold the total value of wine stored for each record.

Construct the 3rd Normal Form (3NF) of the unnormalized Wine file.

Outline why a single-field primary key is not always an appropriate solution for normalized databases.

Identify the steps to create a query to find the names of customers whose account balance is greater than $300 000.

Explain how controlling access rights contributes to the security of the Galaxy Bank database.

Outline what would be necessary to make the above unnormalized table conform to 1st Normal Form (1NF).

Construct the 3rd Normal Form (3NF) of the unnormalized relation shown above.

Explain how row locking ensures the consistency of the data in the Galaxy Bank database.

Explain one benefit of normalizing a database.

Construct the entity relationship diagram (ERD) that shows the relationship between the bank branch, the customers and their loans.

With reference to the table LOANS, state the output of the following query:

SELECT LOANS.Loan_ID, LOANS.Customer_ID, LOANS.Amount, LOANS.Type
FROM LOANS
WHERE (LOANS.Amount > 600000) AND ((LOANS.Type = "Home") OR
(LOANS.Type = "Venture"));

Explain the difference between 2nd Normal Form (2NF) and 3rd Normal Form (3NF).