Deck 1 - Higher Computing - Database Design And Development

Question 1

Why do we need end user / functional requirements?

Answer 1

• 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

Question 2

What are end user requirements?

Answer 2

• What the users want the database to do

Question 3

What are functional requirements?

Answer 3

• What the database must be able to do

Question 4

What is an entity?

Answer 4

• A table that will be used to store data

Question 5

What is an entity occurrence?

Answer 5

• 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

Question 6

What is an attribute?

Answer 6

• Attributes are the fields in each entity occurrence
• For example, an employee would have attributes such as:
  
  • ID number
  • Name
  • Role
  • Salary
    etc.

Question 7

Name the terminology used in an Entity-Relationship Diagram.

Answer 7

• Entity
• Attribute
• Primary key
• Foreign key
• Compound key
• Relationship
• Cardinality

Question 8

What is a primary key?

Answer 8

• A primary key is a field that uniquely identifies a record in a table

Question 9

What is a foreign key?

Answer 9

• A foreign key can be described as a primary key from one table being found in another

Question 10

What is a compound key?

Answer 10

• A compound key is a key made up of two or more primary keys. (e.g. studentID + teacherID)

Question 11

What is a relationship?

Answer 11

• A relationship is a natural association between one or more entities
• For example, Students learn Subjects and Teachers educate Students

Question 12

What is cardinality?

Answer 12

• Cardinality refers to the participants in a relationship
• These include:
  
  • one to one
  • one to many
  • many to many

Question 13

Describe a one-to-one relationship?

Answer 13

• 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

Question 14

Describe a one-to-many relationship.

Answer 14

• 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

Question 15

Describe a many-to-many relationship.

Answer 15

• 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

Question 16

Describe entity relationship diagrams.

Answer 16

• 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

Question 17

Describe entity relationship diagram notation.

Answer 17

• 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

Question 18

Give an example of an entity relationship diagram.

Answer 18

{Mark Appropriately}

Question 19

Describe an entity occurrence diagram.

Answer 19

• 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

Question 20

Give an example of an entity relationship diagram.

Answer 20

{Mark Appropriately}

Question 21

What should the design of the SQL query indicate.

Answer 21

• 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

Question 22

a) What is a wildcard?

b) What are the functions of ‘*’ and ‘?’ in SQL.

Answer 22

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

Question 23

Describe aggregate functions.

Answer 23

• 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

Question 24

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.

Answer 24

• (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