Database Design

Question 1

Define and explain ‘ERD’

Answer 1

Entity relationship diagram - simple visual description of a database

Question 2

State the four components of ERDs

Answer 2

1) Entities
2) Relationships
3) Cardinality
4) Attributes

Question 3

Describe ‘Entities’ in relation to ERDs

Answer 3

The objects that will be linked i.e table or spreadsheet

Question 4

Describe ‘Relationships’ in relation to ERDs

Answer 4

Shows which entities are linked together i.e relationships in Pbi

Question 5

Describe ‘Cardinality’ in relation to ERDs

Answer 5

How many distinct values are within a column. Examples of cardinality are one-to-one, many-to-many, one-to-many etc

Question 6

Describe ‘Attributes’ in relation to ERDs

Answer 6

A single non-zero value within a database (such as a single cell in Excel)

Question 7

Describe a ‘Logical’ data model

Answer 7

A model that maps out maps out what the tables are called and which table is linked to each other.

Question 8

Describe a ‘Physical’ data model

Answer 8

Includes more detail than a logical data model, such as data types, database triggers, storage procedures, access constraints/permissions

Question 9

What does ‘INT’ refer to within database managament

Answer 9

Integer. For no decimal place whole numbers

Question 10

What does ‘VARCHAR’ refer to within database management, and what does it mean.

Answer 10

Variable character. Defines the number of characters the database will accept up to. I.e VARCHAR(6) means that it will only accept entries up to 6 characters

Question 11

What does ‘CHAR’ refer to within database management, and what does it mean.

Answer 11

Character. Defines the number of characters the database will accept ONLY. i.e CHAR (6) means that it will only accept entries of 6 characters

Question 12

What does ‘NVARCHAR’ refer to within database management, and what does it mean.

Answer 12

Unicode variable character. Accepts characters with accents, hebru, arabic characters

Question 13

What does ‘TINYINT’ refer to within database management, and what does it mean.

Answer 13

Tiny integer. Similar to integer, but only stores a low range of numbers, up to 255. If the range is exceeded, it revers to the other side of the range. i.e if you enter 256, it will class it as 0.

Question 14

Describe a ‘relational’ database, and when you would use it.

Answer 14

Organises tables which can be linked/related based on data common to each

Use for small/medium amounts of data in simple formats

Question 15

State the positives (3) and negatives (2) of using a relational database

Answer 15

Pros;
Easy to scale
Support available
Easy to understand structure

Cons;
Can’t use complex data formats such as videos and images
Complex relational databases can become confusing

Question 16

Describe an ‘object-orientated’ database, and when you would use it.

Answer 16

Similar to a hierarchical database, but each part of the table is not a table, but an object (a wrapper that can contain any type of data, i.e photos, videos, tables)

When complex data formats are being used, or when frequent changes are being made.

Question 17

State the positives (2) and negatives (2) of using an ‘object-orientated’ database

Answer 17

Pros;
Easier navigation
Easier to learn, as structure is similar to real world queries.

Cons;
Not suited to simple data as the structure becomes cumbersome
Less tools and support available

Question 18

Describe a ‘No-SQL’ database, and when you would use it.

Answer 18

‘Not-only SQL’. Stores data across distributed servers. Data is stored in nodes that are linked using different attributes, and can accept huge amounts of data in complex formats.

Use it when dealing with large projects and a range of different data

Question 19

State the positives (3)and negatives (1) of using a ‘No-SQL’ database

Answer 19

Pros;
Good for huge amounts of data
Quick to scale/expand with new data in varying formats
Easy to maintain and recover from failures

Cons;
May require significant IT infrastructure

Question 20

Describe a ‘Hierarchical ‘ database.

Answer 20

Structured like an upside down tree - data is accessed through the root, moving down to the specific node. Each node can only have one parent

Question 21

State the positives (2)and negatives (3) of using a ‘Hierachical’ database

Answer 21

Pros;
Simple, organised structures makes it easy to understand
Can achieve high performance as data can be mapped efficiently to memory

Cons;
Less support available
Many data types are not supported
Only supports one-to-many relationships

Question 22

Describe a ‘Network’ database.

Answer 22

Similar to hierarchical databases, but nodes can have more than one parent, and many-to-many relationships are supported.

Question 23

Describe a ‘Multi-dimensional’ database.

Answer 23

All data is stored in a multi-dimensional cube.

Question 24

Describe the dimensions 1-5 in a multi-dimensional database

Answer 24

1 - multiple columns
2 - multiple rows
3 - multiple sheets
4 - multiple boxes (i.e workbooks)
5 - multiple major boxes (i.e 4 boxes)

Question 25

State the positives (3)and negatives (2) of using an ‘multi-dimensional’ database

Answer 25

Pros;

Simple structure
Fast performance
Huge capacity to store data

Cons;

With many dimensions, not all coordinates would be filled - giving a sparsely populated database
Less support due to relatively new innovation

Question 26

Describe the requirements for converting a logical data model into a physical data model

Answer 26

You must know the;
data location
standardised naming conventions
access procedures
primary and foreign keys

Question 27

Describe the ‘first normal form’ in Database normalisation

Answer 27

No repeating or duplicate fields
Each cell contains only a single value
Each record is unique, identified by a primary key

Question 28

Define a ‘compound primary key’

Answer 28

Where the primary key extends across multiple columns

Question 29

Describe the ‘second normal form’ in database normalisation

Answer 29

All non-key fields depend on all components of the primary key