Deck 1 - Higher Computing - Database Design And Development Flashcards
Why do we need end user / functional requirements?
- To clarify the design of the database.
- To identify the features to be implemented on the database
- To evaluate whether the system is fit for purpose after development is complete
What are end user requirements?
- What the users want the database to do
What are functional requirements?
- What the database must be able to do
What is an entity?
- A table that will be used to store data
What is an entity occurrence?
- A specific example of an entity
- For example, John Smith, Mary McLeod and Omar Shaheed are all entity occurrences found in the Student entity; English, Computing and Chemistry are all entity occurrences within the Subject entity
What is an attribute?
- Attributes are the fields in each entity occurrence
- For example, an employee would have attributes such as:
- ID number
- Name
- Role
- Salary
etc.
Name the terminology used in an Entity-Relationship Diagram.
- Entity
- Attribute
- Primary key
- Foreign key
- Compound key
- Relationship
- Cardinality
What is a primary key?
- A primary key is a field that uniquely identifies a record in a table
What is a foreign key?
- A foreign key can be described as a primary key from one table being found in another
What is a compound key?
- A compound key is a key made up of two or more primary keys. (e.g. studentID + teacherID)
What is a relationship?
- A relationship is a natural association between one or more entities
- For example, Students learn Subjects and Teachers educate Students
What is cardinality?
- Cardinality refers to the participants in a relationship
- These include:
- one to one
- one to many
- many to many
Describe a one-to-one relationship?
- In a one-to-one relationship, each entity occurrence in an entity is associated with one, and only one, entity occurrence within a
related entity - For example, a School is managed by one, and only one,
Headteacher, with a Headteacher managing one, and only one,
School
Describe a one-to-many relationship.
- In a one-to-many relationship, each entity occurrence
in an entity can be associated with one or more entity
occurrences in a related entity - For example, a School employs many Teachers and
each of those Teachers is employed by one School
Describe a many-to-many relationship.
- In a many-to-many relationship, several entity occurrences in an entity can be associated with multiple entity occurrences in a
related entity - For example, many Students study several different Subjects and each of those Subjects is studied by many Students
- Direct many-to-many relationships between two entities cannot
be implemented by a relational database system. To overcome this many to many relationships are resolved to one to many
relationships
Describe entity relationship diagrams.
- An entity-relationship diagram is a graphical
representation of the entities in a system - It is used to summarise the relationship that
exists between two or more entities - An entity-relationship diagram indicates:
- The name of each entity in the system
- The name of the relationship between two
entities - The cardinality of the relationship between
two entities - If required, the name of each attribute can
be shown
Describe entity relationship diagram notation.
- The entities on an entity-relationship diagram are
represented by labelled rectangles - If required, the attributes within each entity can be
represented as labelled ovals - The relationship between two entities is represented
by the labelled line which is used to join the entities - Although several different representations can be
used, the entity-relationship diagrams in the examples
below make use of the crow’s feet notation to
indicate the ‘many’ side of a relationship
Give an example of an entity relationship diagram.
{Mark Appropriately}
Describe an entity occurrence diagram.
- An entity-occurrence diagram illustrates the relationships between the entity occurrences of one entity, with the entity occurrences within a related entity
- The creation of an entity-occurrence diagram helps to identify the cardinality of the relationship that exists between the two entities
- In an entity-occurrence diagram, each entity is shown as a tall oval
- Inside each entity, each entity occurrence is represented by the value of its identifier and each relationship is illustrated by drawing a line between associated entity occurrences
Give an example of an entity relationship diagram.
{Mark Appropriately}
What should the design of the SQL query indicate.
- Any field(s) or computated values required
- The table(s) needed to provide all of the details required
- Any search criteria to be applied
- What grouping is needed (if appropriate)
- The field(s) used to sort the data and the type(s) of sort
required - If the field name has spaces or a non textual character
such as # then square brackets must be used
a) What is a wildcard?
b) What are the functions of ‘*’ and ‘?’ in SQL.
a)
- A wildcard character is a character used to replace one or more characters in a string when incomplete information is available and it would be impossible to write a WHERE clause using one of the existing logical operators =, , ≤ or ≥
b)
- ‘*’ is used to represent zero, one or multiple characters missing
- ’?’ is used to represent a single character missing
Describe aggregate functions.
- Aggregate functions operate on a set of rows to return a single, statistical value
- You apply an aggregate to a set of rows, which may be:
- All the rows in a table
- Only those rows specified by a WHERE clause
- Those rows created by a GROUP BY clause (see later)
- In the same way that pre-defined programming functions receive parameter values, SQL aggregate functions require an expression
- This expression is usually a column name but it can be a column name together with an operator
- The following points should be noted:
- SUM( ) and AVG( ) can only be applied to numeric data types; MIN( ) and MAX( ) work with characters, numeric, and date/time datatypes; COUNT( ) works with all data types
- All aggregate functions except, COUNT( ), ignore nulls
- COUNT( ) always returns a positive integer or zero. The other aggregate functions return null if the set contains no rows or contains rows with only nulls.
- An aggregate expression cannot be used in a WHERE clause unless it is a sub query
A charity called Animal Help has a website that allows people to raise funds through sponsorship. Details entered are stored in a relational database. Fundraisers can see their total donations from all of their sponsors and Animal Help can view the funds being raised on their behalf.
State two functional requirements of the relational database.
- (A query to) display the total
- (A query to) display the total donations made to each fundraiser
- (A query to) allow the charity to display total donations made
- (A query to) display the total donations from a sponsor(s)
- (A query to) display the total donations from a fundraiser(s)
- (A query to) insert new fundraisers/sponsors or make a new donation
