Database Design Flashcards
Define and explain ‘ERD’
Entity relationship diagram - simple visual description of a database
State the four components of ERDs
1) Entities
2) Relationships
3) Cardinality
4) Attributes
Describe ‘Entities’ in relation to ERDs
The objects that will be linked i.e table or spreadsheet
Describe ‘Relationships’ in relation to ERDs
Shows which entities are linked together i.e relationships in Pbi
Describe ‘Cardinality’ in relation to ERDs
How many distinct values are within a column. Examples of cardinality are one-to-one, many-to-many, one-to-many etc
Describe ‘Attributes’ in relation to ERDs
A single non-zero value within a database (such as a single cell in Excel)
Describe a ‘Logical’ data model
A model that maps out maps out what the tables are called and which table is linked to each other.
Describe a ‘Physical’ data model
Includes more detail than a logical data model, such as data types, database triggers, storage procedures, access constraints/permissions
What does ‘INT’ refer to within database managament
Integer. For no decimal place whole numbers
What does ‘VARCHAR’ refer to within database management, and what does it mean.
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
What does ‘CHAR’ refer to within database management, and what does it mean.
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
What does ‘NVARCHAR’ refer to within database management, and what does it mean.
Unicode variable character. Accepts characters with accents, hebru, arabic characters
What does ‘TINYINT’ refer to within database management, and what does it mean.
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.
Describe a ‘relational’ database, and when you would use it.
Organises tables which can be linked/related based on data common to each
Use for small/medium amounts of data in simple formats
State the positives (3) and negatives (2) of using a relational database
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
Describe an ‘object-orientated’ database, and when you would use it.
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.
State the positives (2) and negatives (2) of using an ‘object-orientated’ database
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
Describe a ‘No-SQL’ database, and when you would use it.
‘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
State the positives (3)and negatives (1) of using a ‘No-SQL’ database
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
Describe a ‘Hierarchical ‘ database.
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
State the positives (2)and negatives (3) of using a ‘Hierachical’ database
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
Describe a ‘Network’ database.
Similar to hierarchical databases, but nodes can have more than one parent, and many-to-many relationships are supported.
Describe a ‘Multi-dimensional’ database.
All data is stored in a multi-dimensional cube.
Describe the dimensions 1-5 in a multi-dimensional database
1 - multiple columns 2 - multiple rows 3 - multiple sheets 4 - multiple boxes (i.e workbooks) 5 - multiple major boxes (i.e 4 boxes)
State the positives (3)and negatives (2) of using an ‘multi-dimensional’ database
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
Describe the requirements for converting a logical data model into a physical data model
You must know the; data location standardised naming conventions access procedures primary and foreign keys
Describe the ‘first normal form’ in Database normalisation
No repeating or duplicate fields
Each cell contains only a single value
Each record is unique, identified by a primary key
Define a ‘compound primary key’
Where the primary key extends across multiple columns
Describe the ‘second normal form’ in database normalisation
All non-key fields depend on all components of the primary key
