10.1 Fundamentals of Databases Flashcards
What is a data model?
An abstract model of which things to store and what information about them should be recorded. Produced from data requirements.
What is an entity?
In database design it is a thing which data is to be stored.
What is an attribute?
They are characteristics or other information about entities.
What are tables in data models?
How databases are formed which are used to store multiple entities.
What is an entity identifier?
An attribute given to each entity which is unique within that table.
What is a table’s entity identifier?
When a database table has multiple attributes which are combined.
How does are relational databases linked?
By common attributes
What are the 3 possible relationships between tables in a database?
One-to-one, many-to-many, one-to-many. Many-to-one does NOT exist.
What are entity diagrams used for?
To graphically represent the relationships between tables in a database.
What is a primary key?
An attribute that provides a unique identifier for every entity in a database table.
What is a foreign key?
An attribute in a table which is the primary key in another, related table.
What is composite primary key?
When attributes are combined when it is not possible to form a primary key from just one attribute.
What does a link table do?
Links many-to-many relationships
What does it mean to normalise a database?
Ensuring that entities contain no redundant or repeated data.
Why are databases normalised?
So they can be efficient without any compromise to the integrity of their data.
Faster searching and sorting due to the smaller tables created in the normalisation process.
They are easier to maintain.
Duplication of data is minimised and data consistency is improved, helping to reduce the number of update, insertion and deletion anomalies to occur.
What are the 3 levels are normalisation?
First, second and third normal form
What is first normal form?
When a database contains no repeating attributes. The database’s data can be referred to as atomic.
What does atomic mean in first normal form?
Meaning that no single column contains more than one value.
What is second normal form?
A database that satisfies first normal form and partial key dependencies are removed.
When do partial key dependencies in a database occur?
When a database with composite primary keys when a non-key attribute doesn’t depend on the whole composite key.
What is third normal form?
A database that conforms second normal form and does not have any non-key dependencies.
What type of language is SQL and what does it mean?
A declarative language, meaning that the programmer describes the result that’s required rather than describing the process which should be followed.
Describe the SELECT command in SQL?
Used for retrieving data in a database
SELECT <attribute> FROM <table> WHERE <condition></condition></attribute>
Describe the UPDATE command in SQL?
Used in a database for modifying the attributes of an existing entity
UPDATE <table> SET <attribute> = <value> WHERE <attribute> = <value></value></attribute></value></attribute>
Describe the DELETE command in SQL?
Used for removing entities from a database.
DELETE FROM <table> WHERE <condition></condition>
Describe the INSERT command in SQL?
Adds new records to an existing table
INSERT INTO <table> (<column1>, <column2>,...) VALUES (<value1>, <value2>, ..)</value2></value1></column2></column1>
What are wildcards in SQL?
Commands to specify any possible value.
How are wildcards noted in SQL?
Using an astix (*)
What does the CREATE command do in SQL?
Can be used to make a new database table. It specifies the name of the new table, its attributes and their data types. Also specified are entity identifiers like primary and secondary keys.
What are the different data types for the CREATE command in SQL?
Fixed length string, variable length string, integer, number with fractional part, date, date and time, time, year
How do you represent and describe the Fixed length string data type for the CREATE command in SQL?
CHAR (size) - A string with the number of characters specified by size.
How do you represent and describe the variable length string data type for the CREATE command in SQL?
VARCHAR (size) - a string with any number of characters up to the number specified by size
How do you represent and describe the integer data type for the CREATE command in SQL?
INT (size) - A whole number stored using the number of bits specified by size
How do you represent and describe the number with fractional part data type for the CREATE command in SQL?
FLOAT (size, precision) - A number stored using the number of bits specified by size with digits after the decimal point up to the number specified by precision.
How do you represent and describe the Date data type for the CREATE command in SQL?
DATE - A date in the format YYYY-MM-DD
How do you represent and describe the Date and time data type for the CREATE command in SQL?
DATETIME - A date and time combined in the format YYYY-MM-DD HH:MM:SS
How do you represent and describe the Time data type for the CREATE command in SQL?
TIME - A time in the format HH:MM:SS
How do you represent and describe the Year data type for the CREATE command in SQL?
YEAR - A year in one of the two formats YY or YYYY
What benefit does a client server database provide?
Simultaneous access to a database for multiple clients
What is the concurrent access problem?
When different users attempt to access the same field at the same time
What can be the result of the concurrent access problem?
It can result in database updates being lost if two users edit a record at the same time
How can the concurrent access problem be solved?
With the use of record locks, serialisation, timestamp ordering and commitment ordering
How does record locking solve the concurrent access problem?
When a record is accessed by one user, it is immediately locked to other users until the first user has finished using it. Other users are blocked from accessing or modifying the content of a field until it has been unlocked.
How does serialisation solve the concurrent access problem?
Request from other users are placed in a queue. Once the first user has finished using the field, the next command in the queue is executed and so on.
How does time stamping solve the concurrent access problem?
When multiple commands are sent to the same field in a database, each is assigned a timestamp which marks the point in time at which the command was initiated. Commands are carried out on the field in the order of their timestamps.
How does commitment ordering solve the current access problem?
An algorithm is used to work out an optimum order in which to execute commands for the same fields. This algorithm will take into account the impact of commands on other parts of the database and attempt to minimise issues from occurring with the database.
