SLR 23 - databases Flashcards
What are databases used for?
- makes processing data a lot more efficient
- reduces storage requirements
- they avoid redundancy (when piece of data is stored in two or more place)
- allows users to only see relevant data
Why are databases normalised?
- so that they can be efficient without compromises to the integrity of the data
- it ensures that entities contain no redundant or repeated data
- allows for faster searching and sorting because the tables are smaller
- easier to maintain than unnormalized because less duplicated data helping to reduce the number of update, insertion and deletion anomalies that occur.
How is the update command used?
UPDATE <table> SET <attribute> WHERE <attribute> = <value></value></attribute></attribute>
defining a table with SQL?
using the CREATE command - can specify table, attributes and data type and well as entity identifiers
other need to know SQL:
- Fixed length string CHAR(size) - Variable length string VARCHAR(size) - - Integer INT(size)
- Number with fractional part FLOAT(size, precision)
- Date DATE (A date in the format YYYY-MM-DD)
- Date and time DATETIME
(A date and time combined in the format YYYY-MM-DD HH:MM:SS) - Time TIME
- Year YEAR
What are databases?
A structure, persistent collection of data
What do you need to analyse about the data before inputting it into a database?
what is being -
- input
- processed
- stored
- what the data entities are
What are entities?
categories that help organisations structure and record their data
What would be some entities for a library?
- books
- librarians
- rentals
- customers
What are attributes ?
Each entity within a database will then be broken down further into more detail, these are called attributes
What would be some attributes for the entity students?
- date joined school
- F name
- S name
- DOB
- grades
How do you lay out entities and attributes?
- Entity 1(Attribute 1, Attribute2, Attribute3)
- we have to underline our identifier
What is an entity identifier?
- an attribute given to each entity which is unique within that table.
- e.g., CustomerID
What do you have to remember about the primary key?
it has to be underlined
How can databases be related to each other?
common attributes
What are the three possible degrees of relationship between databases?
- one-to-one
- many-to-many
- one-to-many.
What does a one to one diagram?
a straight line
What does a one to many diagram?
a straight line with crows feet at end
What does a many to many diagram?
a lines with crows feet on either end
What is a primary key?
an attribute that provides a unique identifier for the entity in a database table
Why do we need a foreign key?
- when tables are linked by a shared attribute
- the attribute must be a primary key in one table and is called a foreign key in the other
What is a foreign key?
is an attribute in a table which is a primary key in another related table
What needs to be created in a many to many relationship ?
linked tables
What is a table in first form?
contains no repeating attributes or groups of attributes
What is a table in second form?
is in first normal form and contains no partial key dependencies
What is a table in third form?
is in second normal form and contains no non-key dependencies
What is a composite primary key?
when you combine attributes to form it
When does partial key dependency occur?
in databases with composite primary keys when a non-key attribute doesn’t depend on the whole of the composite key.
What rea non key dependencies ?
there are no fields that are dependent on other fields that are not part of the key.
What is SQL ?
the language used with databases
What type of language is SQL?
Structured Query Language is a declarative language - describes the result not the method towards it
What are the 4 main SQL commands?
SELECT
UPDATE
INSERT
DELETE
The select command
is used for retrieving data from a database table
example of how to use the SQL:
SELECT <attribute> FROM <table> WHERE <condition> (ORDER BY <ASC/DEL>) is optional</condition></attribute>
What is the update command used for ?
modifying the attributes of an existing entity
What do update commands usually use to identify which entities to update?
the primary key
How is the delete command used?
is used for removing entities from a database
code for the delete command ?
DELETE FROM <table> WHERE <condition></condition>
How is the insert command used?
to add new records to an existing table
How is the insert command used in code?
INSERT INTO (<column1>,<column 2> , ...) VALUES (<value1>, <value2>, ...)</value2></value1></column1>
