Databases and SQL Flashcards
What is a data model?
An abstract description of the essential data within a given context and how data is related.
What is an entity?
A distinct thing about which data must be stored.
What is an attribute?
A single property or piece of information of an entity.
What is data modelling?
The process of producing an abstract model that represents the organisation and structure of the data
What problems exist in a flat-file database?
1) Data redundancy
2) Data inconsistency
What is an entity identifier?
An attribute or combination of attributes that uniquely identify each instance of an entity.
How is information from a flat-file database represented?
A single table with columns for each attribute and each row representing a new instance of each entity.
What is a composite key?
Multiple attributes are combined to form the table’s entity identifier
What information is stored in a entity description?
Entity name, primary key and attributes
(entity is always singular)
EntityName( PrimaryKey , Attribute1)
- primary key must be underlined
How to create a table in SQL?
CREATE TABLE table_name (
primaryAttribute DATATYPE PRIMARY KEY,
attribute2 DATATYPE CHECK (….)
);
- remember that you can write NOT NULL, DEFAULT constraints
What is an entity-relationship diagram used for?
E-R diagrams are used to graphically represent the relationships between tables in a database
How can we tell from an entity description what the E-R diagram looks like?
The table which contains a foreign key of the other table holds the “many” side of the relationships.
Understanding E-R relationships
- One-to-many: one tutor, many students (and each student has one tutor)
- Many-to-many: one teacher, many pupils - one student, many teachers
- One-to-one: one school, one headteacher
