Database Systems

Question 1

Explain the advantages of using a database system when there is simultaneous
access to the same data.

Question 2

Explain why junction tables, devised to express many-to-many relationships,
should have composite primary keys.

Question 3

Comment on the following statement: “Candidate Keys should use the SQL
constraints of NOT NULL and UNIQUE”.

Question 4

Explain how a B-Tree Index can help improve the performance of a database.

Question 5

Describe how a “Phantom Read” could occur in a transactional database and
suggest a strategy to avoid it.

Question 6

Describe how a “Dirty Read” could occur in a transactional database and
suggest a strategy to avoid it.

Question 7

Describe how a “Fuzzy Read” could occur in a transactional database and
suggest a strategy to avoid it.

Question 8

Do the ANSI level table

Question 9

Explain the use of Triggers in database systems and provide an illustrative
example.

Question 10

Discuss two advantages of using a Relational Database for data persistence, when
compared to using an operating system’s standard file system.

Question 11

Explain why a surrogate key should not be used as Primary Key in tables devised
to express many-to-many relationships.

Question 12

Comment on the following statement: “Adding more Indexes will always make a
Database Management System perform better”.

Question 13

Describe an example of a “Dirty Read” in transactional databases.

Question 14

Comment on the following statement: “Database Management System
Administrators should always define a Transaction Isolation Level of Serializable”.

Question 15

Discuss two circumstances where Stored Procedures might become useful in
Database Systems

Question 16

Explain the advantage of describing Data Models in different levels of
abstraction, such as External, Conceptual, Logical and Internal layers.

Question 17

Describe disadvantages of using Relational Databases.

Question 18

Discuss the disadvantages of using Stored Procedures in Database Systems.

Question 19

List some of the commands belonging to the Data Definition Language (DDL)
portion of SQL

Question 19

Explain the difference between an “SQL INNER JOIN” and an “SQL LEFT
JOIN” CROSS JOIN - RIGHT JOIN

Question 20

What is the main goal of database normalization?

Question 21

Describe the ACID (Atomicity, Consistency, Isolation, Durability) principles of
transactional databases.

Question 22

Difference between 1NF, 2NF and 3NF

Question 23

Redo the ERD Chen Style for:
S2 2023
S2 2024

Question 24

Transform them in Physical Database Tables Give the list of the keys

Question 25

Provide the sample SQL code to create a table “Order”, indicating the respective data types and constraints.

Question 26

WORLD DATABASE QUERIES List all the cities with ‘Population’ of at least 10000.

Question 27

WORLD DATABASE QUERIES List all the countries with ‘Population’ of at least 10 million.

Question 28

WORLD DATABASE QUERIES List all the official languages, i.e. IsOfficial = ‘T’.

Question 29

WORLD DATABASE QUERIES In a single query, list all the countries by ‘Name’ and the number of languages spoken in it, including countries without any language listed.

Answer 29

remember the GROUP BY

Question 30

WORLD DATABASE QUERIES In a single query, list all the countries by ‘Name’ and the number of official languages spoken in it, and present the result in descending order of number of official languages.

Question 31

WORLD DATABASE QUERIES In a single query, list all the countries by ‘Name’ and the sum of ‘Population’ living in cities within it.

Question 32

WORLD DATABASE QUERIES Create an SQL View to list all the cities with ‘English’ as official language (isOfficial = ‘T’).

Question 33

WORLD DATABASE QUERIES Create an SQL View “AmericaRegions” to hold all the Regions in mainland America.

Question 34

WORLD DATABASE QUERIES Create an SQL View “Capitals” to hold all the Capital city names in the world, sorted alphabetically in ascending order.

Question 35

Store Procedures give an example of the structure.

Question 36

WORLD DATABASE QUERIES Create a Stored Procedure to list the cities whose names start with the letter ‘D’, in ascending alphabetical order.

Question 37

WORLD DATABASE QUERIES Create a trigger to check if, before inserting a new country population, the value is not negative, and if it is negative, set it to zero.