DDD

Question 1

What information should be included in an entity relationship diagram (ERD)?

Answer 1

• Entity names
• Entity types (strong - normal line and normal box, weak - dashed line and double box)
• Attributes (PK - underlined, FK - starred)
• Relationship participation (mandatory - line, optional - circle)
• Relationship name (add a word/sentence)
• Cardinality (any side with many needs crow’s feet)

Question 2

What is a surrogate key?

Answer 2

A single field used as a unique identifier which is not derived from the data in the table

Question 3

Why is a surrogate key used?

Answer 3

Usually used to replace a compound primary key to improve performance of queries involving inner joins and make accessing historical data easier

Question 4

What information needs to be included in a data dictionary?

Answer 4

• Entity name
• Attribute name
• Primary or foreign key
• Attribute type (can be varchar, integer, float, date, time)
• Attribute size (for varchar)
• Validation (can be presence, range, restricted choice, or field length)

Question 5

Which SQL operations are required?

Answer 5

• CREATE (for database or for table with primary and foreign keys specified, not null, check, and autoincrement)
• DROP (any tables and then database)
• SELECT (fields FROM tables WHERE condition GROUP BY field HAVING aggregate function condition
• INSERT (INTO table (fields) VALUES (values))
• UPDATE …

Question 6

What are the SQL wildcards?

Answer 6

• • all (e.g. SELECT * FROM Cars means select all fields in the cars table)
    % - zero or more characters
    _ - 1 character (e.g. WHERE field LIKE ‘%a_’ would choose anything with a as the second last letter)

Question 7

What does ANY do in a WHERE clause?

Answer 7

Applies a condition to everything in brackets, e.g. WHERE field < ANY(a, b, c) would return true if the field was smaller than any of a, b, and c

Question 8

What does EXISTS do in a WHERE clause?

Answer 8

Returns true if something (usually the table returned from a sub-query) exists, e.g. WHERE EXISTS (SELECT * FROM Cars WHERE Manufacturer = ‘Tesla’) would return true if there are any Teslas in Cars

Question 9

What does an entity occurrence diagram look like?

Answer 9

Ellipses labelled with the names of the entity (table) they represent, each containing their primary keys and straight solid lines connecting the entities to show the primary and foreign key relationships and their optionality and cardinality

Question 10

How are many to many relationships implemented in a database system?

Answer 10

They can’t be directly implemented so a linking entity is needed which at a minimum would contain a compound key of the primary keys of the two entities being connected, resolving the many to many relationship through two many to one relationships

Question 11

What is the difference between a strong and weak entity?

Answer 11

A strong entity does not rely on another entity for identification - it has a unique primary key of its own attributes; a weak entity depends on another entity to exist - it must make use of the primary key of another entity to form a unique identifier

Question 12

What is shown on a data dictionary?

Answer 12

Entity name (e.g. Entity: Resort), column heading (Attribute name, Key, Type, Size, Required, Validation), then for each attribute name the columns are filled in: Key can be blank, PK, FK, or PK/FK, type can be integer, varchar (if there is no size column then do e.g. varchar(20)) required is yes or no for the presence check, field length validation is Length </=/> x (for varchar inputs), restricted choice validation is Restricted choice: a, b, c, range validation is Range: >= x and <= y, Auto increment would also be in the validation column

Question 13

What can be included in a query design?

Answer 13

Field(s)/calculation(s), Table(s) query(-ies), Search criteria which can include an inner query with its own Field(s)/calculation(s), Table(s), Search criteria, then Grouping, Having, and Sort Order

Question 14

When is HAVING used in a query?

Answer 14

Like WHERE but with aggregate functions, e.g. HAVING Count(*) > 3

Question 15

How do you insert data into a table?

Answer 15

INSERT INTO tableName (col1, col2, …) VALUES (val1, val1, …);

Question 16

How do you change the data in a table?

Answer 16

UPDATE tableName SET col1 = val1, col2 = val2, … WHERE condition;

Question 17

How do you remove a record from a table?

Answer 17

DELETE FROM tableName WHERE condition;

Question 18

How do you create a database?

Answer 18

CREATE DATABASE databaseName;

Question 19

How do you create a table?

Answer 19

CREATE TABLE tableName (
fieldName1 INT AUTO_INCREMENT,
fieldName2 VARCHAR(20) NOT NULL,← for presence check
fieldName3 BOOL,
fieldName4 FLOAT,
fieldName5 DATE, ← YYYY-MM-DD
…
PRIMARY KEY(fieldNameA, fieldNameB), ← for multiple field primary key
FOREIGN KEY(fieldName) REFERENCES tableName(field),
CHECK(fieldName condition) ← could be </=/>, BETWEEN,
)

Question 20

How do you delete a table?

Answer 20

DROP TABLE tableName;

Question 21

How do you delete a database?

Answer 21

Make sure all tables are dropped and then
DROP DATABASE databaseName;

Question 22

What is the structure of a SELECT query?

Answer 22

SELECT list of field names
FROM list of table names
WHERE condition
GROUP BY list of field names
HAVING condition
ORDER BY list of field names

Question 23

How is BETWEEN used in a SELECT query?

Answer 23

WHERE fieldname BETWEEN val1 AND val2