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
