ТВОЙ BZFAR
Главная » Статьи » Database » Relational databases
Relational databases

Relational databases

Learning objectives

11.4.1.1 describe relational databases and their purpose

11.4.1.2 use the terms "table", "attribute", "record", and "index" to describe databases

11.4.1.3 explain the difference between primary, foreign and composite keys

1. What is a relational database?

A database is an organised collection of data. A relational database represents data in tables of rows and columns. Related tables can be linked through matching key values.

It helps a program store, retrieve and update related information. A school club database can answer: Who joined Robotics? or Which clubs did student 101 join?

A well-designed database can reduce repeated facts: store a club's name once in Clubs and refer to it by ClubID in Enrolments. Key constraints help keep these references valid.

Open the original lesson video on YouTube.

Database, DBMS and phpMyAdmin

The database is the organised data. A DBMS is software that manages databases; MySQL and MariaDB are examples. phpMyAdmin is a web interface for administering MySQL or MariaDB. XAMPP is a local development package containing Apache, MariaDB, PHP and Perl. XAMPP downloads · About phpMyAdmin.

2. Table, attribute and record

The following school club data are illustrative. Each table describes a type of thing, such as students, clubs or enrolments.

  • Table: a named collection of records arranged in columns.
  • Attribute (column or field): a named characteristic, such as FirstName.
  • Record (row): the stored values about one item, such as one student.
Students · 3 records, 3 attributes
StudentID PK FirstName Class
101 Ayan 11A
104 Dana 11B
108 Ayan 11A

Select a button to highlight a component. StudentID is the chosen primary key.

Use precise language: FirstName is an attribute; Dana is an attribute value. The whole row 104, Dana, 11B is a record. A name can repeat, so the system uses StudentID to distinguish students.

3. Primary, foreign and composite keys

Term Meaning Example
Primary key (PK) The chosen attribute or group of attributes that uniquely identifies each record. Every component must have a value: no NULL. Students.StudentID identifies a student. Clubs.ClubID identifies a club.
Foreign key (FK) An attribute or group of attributes that references a primary key or another declared unique key. Referencing values may repeat. Enrolments.StudentID references Students.StudentID.
Composite key A key made from two or more attributes considered together. Enrolments uses the pair (StudentID, ClubID) as its composite primary key.

Primary and foreign describe a key's role. Composite describes its use of multiple attributes. One key can therefore be both primary and composite.

Clubs
ClubID PK ClubName
C01 Robotics
C02 Music
Enrolments · PK = (StudentID, ClubID)
StudentID
FK + PK part		 ClubID
FK + PK part		 Role
101 C01 Member
101 C02 Member
104 C01 Captain
108 C02 Member

Enrolments.StudentID → Students.StudentID
Enrolments.ClubID → Clubs.ClubID

In this system, a student may join several clubs, but can join each particular club only once. StudentID 101 repeats, and ClubID C01 repeats. Each complete pair is different.

One composite primary key: (StudentID, ClubID) identifies an enrolment. Its two attributes also act as two foreign keys. A table has at most one primary-key constraint, which may contain several attributes.

Match the ID values to find the student's enrolments and club names.

What happens when a referenced record is missing?

A non-NULL foreign-key value must match a referenced key. StudentID 999 cannot be used here because Students has no such record. A foreign key can allow NULL when the schema permits it. In this example, both foreign keys are also components of the primary key, so neither can be NULL. Deletion of referenced records follows rules defined in the schema.

4. What is an index?

An index is an additional data structure built from one or more attributes that helps a DBMS locate matching records. Some indexes also help with sorting.

Like a book index, it provides a route to relevant entries. An ordinary index can contain repeated search values. It needs storage and maintenance when the data changes; a DBMS decides whether using it will help a particular query.

A primary key defines record identity. An index supports lookup; a DBMS may use a unique index to enforce a primary key. Row numbers displayed by an interface are only display labels.

Conceptual index on Enrolments.ClubID
Search value Matching enrolment keys
C01 (101, C01), (104, C01)
C02 (101, C02), (108, C02)

This simplified lookup guide shows references to records, not row positions or measured query times.

5. Key laboratory

Try adding an enrolment. The model checks the two foreign keys and the composite primary key. The example Students and Clubs tables stay unchanged.

Interactive controls require JavaScript. If this notice remains visible, the script is not running. The theory, tables and answer explanations are still available.

Try (104, C02), then repeat it. Next try a missing ID or NULL.

Lab Enrolments · 4 records
StudentID ClubID Role
101 C01 Member
101 C02 Member
104 C01 Captain
108 C02 Member

This is a classroom model of key constraints. Lab changes last until reset or page reload.

6. Exercises

There are eight activities: the two original LearningApps exercises and six checked tasks below. LearningApps results are separate from the progress counter.

Ex. 1 · Terms and definitions (LearningApps)

Open Ex. 1 on LearningApps.

Ex. 2 · Components of a database table (LearningApps)

Open Ex. 2 on LearningApps.

Completed: 0 / 6 checked tasks

Answers and progress are kept in this browser when storage is available. They are not sent to a teacher.

Ex. 3 · Purpose of a relational database Not checked
Select all suitable reasons for using a relational database for school clubs.

 

Hint

Think about storage, relationships and consistency.

Explanation

Choose structured tables, combining related information, and valid links. Separate Students and Clubs tables let us store each student or club fact in one place instead of copying it into every enrolment.

Ex. 4 · Table, attribute, record and index Not checked

Use the fixed Students table in section 2. Choose a term for each description.

 

Hint

A row contains one record. Count columns using their headings, not individual cells.

Explanation

Table; attribute; record; index. Students has 3 records and 3 attributes. The value Dana is one attribute value, while the full row 104, Dana, 11B is a record.

Ex. 5 · A suitable primary key Not checked

The system assigns a different, non-missing StudentID to every student. Names and class labels may repeat.

Choose the most suitable primary key for Students.
Select the two requirements for primary-key values.

 

Hint

Use a stable identifier. A primary key may consist of more than one column.

Explanation

StudentID fits the stated rules. A primary key identifies records uniquely and has no NULL component. It can be textual or numeric, and can be a single attribute or a combination. FirstName and Class may repeat.

Ex. 6 · Follow foreign keys Not checked

An Enrolments record contains StudentID = 104 and ClubID = C01. Follow its references to Students and Clubs.

 

Hint

Match ID values across tables. A matching name is not the link.

Explanation

Dana; Robotics; reject. StudentID 104 identifies Dana, and C01 identifies Robotics. A non-NULL foreign-key value must match the referenced key. Foreign-key values can repeat across enrolments.

Ex. 7 · A composite primary key Not checked

Use this fixed snapshot, regardless of changes in the lab:

(101, C01), (101, C02), (104, C01), (108, C02)

Each pair is StudentID, ClubID. A student can join a particular club only once.

 

Hint

Check the whole pair. Role is outside the chosen primary key.

Explanation

Use StudentID and ClubID together; accept (104, C02); reject another (101, C01). Individual IDs can repeat, but their combination must be unique. Changing Role does not change that key. The pair forms one composite primary key.

Ex. 8 · An index and its trade-off Not checked
Select all correct statements about an ordinary, non-unique index.

 

Hint

Separate the identification rule from the lookup structure.

Explanation

Choose lookup, storage/maintenance, and repeated search values. An ordinary index can point to several matching records. A primary key specifies record identity; a DBMS may use a unique index to enforce it. These are different roles.

7. Exam-style practice

Write complete explanations before opening the suggested answers. These are practice questions written for this lesson.

1. Describe a relational database and one benefit for the club system.

It organises data in related tables of rows and columns. Matching keys let the system combine information, for example student names and the clubs they joined. Storing club details once can reduce repeated data.

2. Explain why StudentID alone cannot identify each Enrolments record.

A student can join several clubs, so StudentID repeats in Enrolments. The combination (StudentID, ClubID) identifies one enrolment because each student can join a particular club only once.

3. Explain the difference between a primary key, a foreign key and an index.

A primary key identifies each record uniquely. A foreign key references a key in a related table and keeps the link valid. An index is a lookup structure that can improve searches, with storage and maintenance costs.

Language support for explanations
  • A relational database stores ... so that ...
  • FirstName is an attribute, whereas the row ... is a record.
  • StudentID alone is unsuitable here because ...
  • The pair is unique even though ...
  • The foreign key references ... and prevents ...

Further study: Primary key & Composite key · Data types.

References
Категория: Relational databases | Добавил: bzfar77 (11.10.2020)
Просмотров: 14916 | Теги: primary key, tuple, Object, Table, RECORD, index, database, composite key, Attribute, foreign key | Рейтинг: 5.0/5
Всего комментариев: 0
Войдите:
avatar