The Basics of Databases

Simple Database

 The simplest kind of database is a flat file, consisting of information about a single entity.




Entities

 An entity is a category of object, person, event or thing of interest about which data needs to

be recorded. Eamples of entities would be: Customer, Product or Subscription (for a digital textbook service).




Records

 In a database, a record is a group of fields within a table that are belong to a specific entity.

The different fields in a record for the digital textbook example could be:

  • Customer (custID, title, firstname, surname, email)
  • Product (productID, title, subject, level, price)
  • Subscription (subID, startDate, endDate)

Primary Key


Each entity needs an identifier which uniquely identifies a particular record. This is called a Primary Key. 

Each Primary key is totally unique for every record.



Composite Primary Key

Sometimes multiple unique IDs are required to identify a record (if there was a order number and product number for example). These are called Composite primary keys. 

When writing Primary Keys in an exam situation you identify them by underlining them.



Secondary Key

Secondary keys are a second way of searching a database. They are not unique to a record but will narrow down results. For example a book author's name good be searched in a library database. 





Entity Relationships

Entities are usually linked in some way. This is represented using diagrams. 



We can say, for example, that one school has many pupils, or many pupils attend one school.



A customer can have multiple subscriptions (many subscriptions), of which each subscription is linked to one product. 



Foreign Key

A foreign key is an attribute that creates a join between two tables (relations). 



Referential Integrity

This means that no foreign key in one table can reference a non-existent record in another. 

For example: it should not be possible to add a subscription for a customer with custID C100 if there
is no record for customer C100. 



Relational Databases


Data is held in separate tables are linked by common attributes.

A row on a table is called a record.

The columns in the tables represent different attributes.



Indexing

Database software automatically keeps a record of all Primary keys. This allows for records to easily be searched for.

Sometimes other terms can be used to search the table. Such as secondary keys (book author for example). This will speed up and make searching easier.


Normalisation

Normalisation is used to come up with the best possible design for a database.

This means data is not duplicated, data is consistent throughout the database and the structure should be flexible to allow the user to carry out complex queries. 

Comments

Post a Comment

Popular posts from this blog

CPU Fetch-Decode-Execute Cycle

Image
A CPU (central processing unit) executes and manages the rest of the hardware inside a computer system - it is the brain of a computer! The CPU is made up of the main memory, the processor and the cache memory. It's job is to process data (by processing we mean searching, sorting, calculating and decision making). The CPU follows the Fetch-Decode-Execute cycle. The Fetch Stage: the CPU use a piece of hardware called the ' address bus ', this is where the CPU asks the RAM for some data. The RAM then responds with this data on the ' Data Bus '.  The Decode Stage: This is where the CPU converts the instructions in to a way in which it understands it. It then gets things ready for the next step.  The Execute Stage: This is where data processing happens. Instructions are carried out on the data, as soon as the command is completed it starts the next one. 
Read more

OSI Network Model

Open Systems Interconnection (OSI) model - a conceptual framework that describes the functions of a networking or telecommunication system. The OSI model has 7 layers.  Layer 7 - Application Layer This is what the user communicates with. It is the applications that we used - Google Chrome & Skype are examples.  Layer 6 - Presentation  This is where data is converted from the application framework into something that the network understand. The data would be encrypted here for example.  Layer 5 - Session Computer need to create a session between the two devices so they can speak to each other. This is done here. The computer communicate telling each other how much time they should wait for a response from each other and what they should do when the task has been completed (end session) etc.  Layer 4 - Transport  The Transport Layer deals with the coordination of the data transfer between end systems and hosts. This...
Read more

Scheduling

Image
Scheduling is where a computer prioritises tasks to be carried out in a certain order to maximise the use of the computer and all of its peripherals. The algorithm must be robust so the computer does not freeze and also so every task/user gets a fair response time. Two main types of jobs are I/O bound jobs and processor bound jobs: I/O bound jobs (input/output) examples: Sending a document to the printer. Moving the mouse. Typing on the keyboard. Processor bound jobs examples: Playing Music Watching a video Calculating in Excel. There are different ways a processor can prioritise tasks: FCFS (first come first served) - tasks are started and completed in the order they are received. RR (round robin) – the time slice is 1 minute - each job has a minute spent on it before moving onto the next one. This occurs in a circuit until all jobs are complete. SJF – shortest job first - The job with the shortest completion time is carri...
Read more