Question 1

What is a DBMS?

Accepted Answer

Database Management System - a software designed to assist in maintaining and utilising large collections of data

Question 2

What are some advantages of using a DBMS?

Accepted Answer

Data organization and management, reduced data redundancy and inconsistency, multi-user access, data integrity and accuracy

Question 3

What is a relational database?

Accepted Answer

Collections of n-ary relations where the associations forming the relation are represented as tables.  Each table represents a relation, each column represents a domain (“attribute”), each row represents a specific association, case or tuple (“record)”

Question 4

What is redundancy and why may it arise?

Accepted Answer

The storing of the same information more than once. This may happen due to repeating data, storing derived attributes, adding checksums, etc.

Question 5

What is a DBMS transaction?

Accepted Answer

A unit of work executed by a DBMS, ie any manipulation of the database. A transaction may involve one or more operations, with either all or none executed.

Question 6

What are the desirable properties of a transaction?

Accepted Answer

ACID - Atomic (treated as a unit), Consistent (Guarantee database remains correct), Isolated (Even if executed concurrently, database is left in same state that it would have), Durable (Effects remain even in system failure)

Question 7

What is an ER model?

Accepted Answer

An Entity Relationship model is a graphical representation of a database that shows relationships between pieces of information. It helps to understand structure of data, how they connect, and what rules apply.

Question 8

In an ER model, how are single valued, multi valued, key and derived attributes respectively represented?

Accepted Answer

Singlevalued: Ellipses
Multi valued: Double ellipses
Key: Names underlined
Derived: Dashed ellipses

Question 9

What are the common cardinalities of entity relationships, and some examples for each?

Accepted Answer

1:1 - One person has one passport
One -to - many - One lecturer teaches many modules.
Many - to - many - A student can take many modules, modules have many students

Question 10

What is JDL?

Accepted Answer

JHipster Domain Language. It is domain-specific used by JHipster to describe an application’s domain model.

Question 11

List the benefits of using JDL.

Accepted Answer

Text-based, easy to modify, version-control friendly, less ambiguous, integrates directly with JHipster.

Question 12

If you had an entity student, with attributes name, email and date of birth, how would you set that up with a JDL model?

Accepted Answer

entity Student {
           name String
            email String
           dateOfBirth LocalDate
}

Question 13

What are some primitive modifiers used in JDL?

Accepted Answer

unique - values must be unique across all instances
required - value is mandatory and cannot be null
minlength(n) and maxlength() - length constraints
min(x) and max(y) - value constraints

Question 14

Using JDL relationship syntax, how would you set up a one to many relationship between lecturer and modules?

Accepted Answer

relationship OneToMany {
       Lecturer{modules} to Module{lecturer}
}

Question 15

How do you setup an enum in JDL? (e.g an orderstatus with pending, shipped, delivered and cancelled statuses)

Accepted Answer

enum OrderStatus {
        PENDING,
       SHIPPED,
        DELIVERED,
       CANCELLED,
}

Question 16

Why do we need database normalisation?

Accepted Answer

Makes the database more efficient, prevents duplicates, ensures consistency and relevance, ensures accuracy, reduces storage time, optimise query execution times, etc.

Question 17

What is needed for a table to be in 1NF?

Accepted Answer

A single cell must not hold more than one value, there must be a primary key, no duplicated rows or columns, each column must have only one value for each row in the table.

Question 18

What is needed for a table to be in 2NF?

Accepted Answer

Must be in 1NF and each non-key attribute must be functionally dependent on the entire primary key. This is a relationship where one attribute determines the value of another attribute.

Question 19

What is needed for a table to be in 3NF?

Accepted Answer

Must be in 2NF and have no transitive functional dependencies. This means every non-key attribute must depend on only the primary key, and no other non-prime attributes.

Question 20

How might you use SQL commands to create a table called room with room_id, room_name, room_capacity and room_zoom?

Accepted Answer

CREATE TABLE public.room
(
            room_id text NOT NULL,
            room_name text,
            room_capacity integer,
            room_zoom text,
            PRIMARY KEY (room_id)
);

Question 21

What are CRUD commands in SQL?

Accepted Answer

Creating data, Reading data, Updating data and Deleting data.
In SQL, these are performed by INSERT, SELECT, UPDATE and DELETE.

Question 22

If you had a table named albums, with attributes album_name and tracklist_length, how would you insert "Wishbone" and "12"?

Accepted Answer

INSERT INTO public.albums (
         album_name, tracklist_length
)
VALUES ("Wishbone", 12);

Question 23

If you had a table named albums, with an attribute tracklist_length set to 12 for an album_name "Wishbone", how would you update the tracklist length to be 17?

Accepted Answer

UPDATE public.albums SET
            tracklist_length = 17
WHERE album_name = "Wishbone";

Question 24

If you had a table named albums, how would you select all albums which have a tracklist_length of 10?

Accepted Answer

SELECT * from public.albums WHERE
            tracklist_length = 10;

Question 25

If you had a table named albums, with an album_name attribute "Wishbone", how would you delete this from the table?

Accepted Answer

DELETE FROM public.albums WHERE album_name = "Wishbone";

Question 26

In PostgreSQL, how do you create a role named testuser with password 'password'?

Accepted Answer

CREATE ROLE testuser
             NOINHERIT LOGIN PASSWORD 'password';

Question 27

In PostgreSQL, how do you create a database named todo_list with owner as testuser?

Accepted Answer

CREATE DATABASE todo_list
                       WITH
OWNER = testuser
ENCODING = 'UTF8'
CONNECTION LIMIT = -1;

DBWP

Databases and Web Programming

"Know" box contains:
Time elapsed:
Retries: