↑
Top 

week 2

Databases and relationships

Types of databases, e.g.:

● local

 When looking at local databases, we tend to use software that's installed onto a PC. Examples of local software Would be Microsoft Access. This software will allow a user or developer to create a database that sits on the computer for only the use of those people accessing that computer.

● online (web)

When we look at databases that are web-based, we can use websites like Amazon or eBay As examples. These websites have a large amount of information that the website will pull up on using a online database. There are a number of different web-based databases around, one of which is MYSQL.

 

● flat file (contains a single table of information)

When we refer to a flat-file database, this usually represents a database that has only one table. Within this table, every piece of data is entered and kept. This is not the most efficient use of a database as this will allow for data duplication and data redundancy to occur.

 

● relational (uses common identifiers found within a data set consisting of two or more related tables).

Relational Databases are the preferred type of database that is created by developers. These sorts of databases will allow for fewer bits of data to be duplicated or made redundant. These databases are much more efficient than Flat file databases.

 

Types of relationships, e.g.:

● one-to-one is a relationship between one record in the first table that corresponds to exactly one record in the related table

 

● one-to-many is a relationship where each record in the first table may have many linked records in the related table, but will still have only one corresponding record in the first table

 

● many-to-many is a relationship where each record in the first table may have linked records in the related table and vice versa.

 

---

 

Learning aim B: Design a relational database

Designing a database

Designs should be based on the intended purpose and user requirements defined in a brief to solve a problem. Design documentation, including:

 

Hardware, software and other resources required

When using or creating any database we need to consider the tools that we need. Our first area to look in to is the "Hardware". Hardware is anything that you can physically touch this could be any of the following;

• Desktop/Tower PC or Laptop
• Mouse 
• Keyboard
• Modem/Router

These all play an important role in enabling us to be able to create or use a database, without them, we will struggle.

Next, we must look at the "Software" this is something that we cannot touch and is most often computer programs and code. There are a number of software elements that we require to create or use a database these are;

• Operating Systems (Windows 11, Mac OSX)
• Microsoft office suite
• Microsoft Access
• Web browser

 

Entity Relationship Diagram (ERD), including entities, attributes and relationships

What is an Entiry Relationship and how do we show this in a diagram? An entity-relationship is the link between database tables and different types of links that are needed.

Within a database, we can create 1:1 (One to One), 1:M (One to Many) and M:M (Many to Many) links.

The links will indicate the following;

1:1 - One record may appear in one table only

1:M One record may appear many times in a table

M:M Many records may appear in a table many times. 

Validation, Verification Procedures and Data Dictionaries

 

Table Name	tbl_Employee
Description	Table to store employee details
Field Name	Data Type	Primary/ Composite Key (Y/N)	Foreign Key (Y/N)	Field Size (if applicable)	Format/ Input Mask (if applicable)	Validation (if applicable)
Employee_ ID	Auto number	Yes	No	No more than 8 charters	NA	Is Not Null
Employee Name	Short Text	No	No	25	>L	Is Not Null
Employee DOB	Time/Date	No	No		DD/MM/YYYY
Employee Telephone	Short Text	No	No	11	NA
Gender	Short Text	No	No	1	NA	"M" or "F"

 

Input and output screens/forms and reports

 

Constraints (e.g. hardware and software availability)

There are a few constraints when it comes to the creation of a database, one very obvious example is COST. The tools that you are likely to be using during this unit will be Microsoft Access. This product is not a free one and requires either a one-off payment or a yearly subscription to Office 365. However, this is not the only database out there, you have Oracle, MYSQL and others that are either free or have a very large cost.

All these software applications require some sort of operating system to run on, and, computers of a specific hardware specification.

 

Test plan with test data to test functionality

Test number	Input for tester (Merit)	What is being tested?	Expected outcome	Actual outcome	Comments
1	1	Patient ID – auto number doesn’t allow data entry and automatically allocates the next number.	Data entry not allowed.
1
2
3
4
5
6
7
8
9
10

 

A brief outline of alternative design ideas.

When creating your work/designs toward the solution of the assignment you might want to consider the options that you have given, could they be changed, improved or presented in an alternative way. Once you have shown these alternative designs discuss what they are and why they are different to your initial designs. This will enable the discussion later why you selected the alternative designs over your initial designs when it comes to the creation of the database.

 

Last Updated
2022-05-26 08:54:51

---

Links to Learning Outcomes		Links to Assessment criteria
Learning aim B: Design a relational database		1A.1 Identify the uses of databases and how the tools/techniques are used in two different databases. 2A.P1 Explain the uses of databases and how the tools/techniques are used in two different databases. 2A.M1 Review how the tools/techniques are used in two databases to improve productivity, accuracy and usability. 2A.D1 Discuss the strengths and weaknesses of the databases.   1B.2 Identify the purpose and user requirements for the database. 1B.3 Produce a design for a database with guidance, including a single table database structure with a data entry form. 2B.P2 Describe the purpose and user requirements for the database. 2B.P3 Produce a design for a relational database, including: ● a database structure ● a test plan. 2B.M2 Produce a detailed design for a relational database, including: ● alternative designs ● a detailed database structure ● test data. 2B.D2 Justify final design decisions, explaining how the relational database will fulfil the stated purpose and user requirements, and any constraints in the design.

---

English and Maths Coverage

---

Stretch and Challenge

---

Homework

---

How 2's Coverage

---

Equality and Diversity Calendar

Files that support this week

---

Democracy: | Rule of Law: | Mutual Respect: | Individual Liberty: | Acceptance of Difference: | College Values:

Week 1←
PrevWeek 2←
PrevWeek 3←
Prev→
Next→
Next