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.

State what is meant by redundant data in databases.

Explain one issue that can be caused by redundant data in 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"));

Identify an appropriate data type for Prod_Price.

Outline the redundant data in this relation.

Outline two reasons why databases are normalized.

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

Some data in the MRBS database is redundant.

Outline one problem caused by redundant data.

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

Identify two characteristics of a conceptual schema.

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

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

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

Explain one benefit of normalizing a database.

Describe the nature of the data dictionary.

Identify three characteristics of logical schema.

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.

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.

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

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 how the Billetmania database management system ensures that a seat is not booked by two people simultaneously.

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

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 two different types of relationships within databases.

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

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

Outline two issues caused by storing redundant data.

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.

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

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

State the result from the following query:

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

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).

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

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

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

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

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

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

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

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

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

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

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

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.

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 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.

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

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

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

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.