Week 1 - ER Model Flashcards
What is a database?
Stores large amounts of data.
From a software perspective, what can databases do?
It’s an expensive piece of software that provides the following functionalities:
To model data (relational data modelling, object oriented data modelling), access data, analyse data, store data and secure data
From a software perspective, what can databases do?
6 things.
1) model data
2) access data
3) store (physically) data
4) secure data
5) maintain data consistency
6) optimize data access
From a user perspective, what can databases do?
Provides a black box for users so that they can perform:
1) data modelling
2) work with SQL (a declarative programming language) to manage and query data. Declarative means we tell the DB what to do, not how to do it.
If a user is using SQL to communicate with the database, what thing bridges the user to the rest of the system?
The application
What does the parser component do?
checks for any syntax errors
What does the optimiser component do?
Tries to find the best sequence of the underlying algorithms and how it’s going to solve the request from the user.
What happens after the optimiser can found the best optimisation sequence to use?
After finding the best optimization sequence of the processing algorithms, then the system invokes all these processing algorithms with access to the computational resources: the CPU, the memory, the data files and so on so forth. Then with an interaction with the OS, our query is executed.
What are the six steps that happen when a user writes a SQL query?
1) Application
2) parser
3) optimiser
4) processing algorithms
5) CPU/memory/data files/OS
6) Code generator
What are the three families of data?
1) Structured data
2) Unstructured data (there is no meta-data to interpret the data)
3) Semi-structured data (XML/JSON)
What are the two approaches to conceptual data modelling?
1) Entity Relationship (E-R) modelling
2) Relational Modelling (purely maths driven)
What is a SQL schema?
A SQL schema is made when we take the relational model and write down specific statements written in SQL. Once the schema is created, we can start querying in SQL.
What are the components of the ER model?
1) entities
2) attributes
3) relationships
How do you identify an entity in the ER model?
Entities are represented in a box with rounded corners
What is the instance of an entity?
It materialises an entity, e.g. “Chris” is an employee
How do you represent an attribute in the ER model?
Attributes are represented in an oval shape.
What instantiates an attribute in the ER model?
A value.
e.g. name = “Chris”
After we have instantiated an attribute by assigning it a value in the ER model, what do we get?
A tuple. A bunch of values where each value corresponds to a specific attribute.
How are relationships in the Entity Relationship model represented?
We represent relationships between entities in diamonds. Roles are optional and are the noun for the verb relationship. They’re written on the line.
What does relationship cardinality mean?
It’s the number of instances that an entity participates in a relationship.
E.g. if entity A = manager
if entity B = project
The relationship cardinality is 1 to 1, because a manager can only manage one project.
What are four types of relationship cardinality?
1:M one to many
M:1 many to one
1:1 one to one
M:N many to many
What happens when we split a many to many relationship?
N:M relationship always splits into two one-to-many (1:N and M:1) relationships, by transforming the original one into a new Relationship Entity.
