10.1 Fundamentals of Databases Flashcards
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.
How 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 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 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.
What is third normal form?
A database that conforms second normal form and does not have any non-key dependencies.
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>
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.
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.
