1.3.2 - Relational databases, keys and relationships
A lending club needs to store members, equipment and loans without confusing one person or item with another. You will organise these facts into linked tables, read the relationships between them, and choose ways to capture and exchange the data.
Separating facts into tables
A database is an organised collection of data which can be stored, searched and maintained.
A table describes one kind of entity, such as Member. A record is one row describing one occurrence, and a field is an attribute such as Name. A schema specifies the structure: table and field names, data types, keys and constraints. A database management system (DBMS) manages operations on this data, including queries and authorised changes.
A simple flat file holds records in one structure, for example a CSV with MemberID, MemberName, ItemID, ItemTitle and LoanDate in every loan row. It is easy to exchange and may be sufficient for a small one-purpose list. But a member's name is repeated for every loan. Updating only some copies creates inconsistent names; adding a member with no loan may require an inappropriate empty loan row. Flat files do not inherently provide relationships, shared transactions or constraint enforcement, though an application can implement its own checks.
A relational database stores related data in tables and connects records through matching key values. Here are three small tables. Each loan concerns exactly one member and one item; a member or item can occur in multiple loans over time.
| MemberID | Name |
|---|---|
| M01 | Hana |
| M02 | Luis |
| ItemID | Title |
|---|---|
| I10 | Camera |
| I11 | Tripod |
| LoanID | MemberID | ItemID |
|---|---|---|
| L90 | M01 | I10 |
| L91 | M01 | I11 |
| L92 | M02 | I10 |
Hana's name is stored once in Member. To find the borrower for L91, follow its MemberID M01 to that Member record. The repetition of M01 in Loan is necessary to express the separate loans; avoiding unnecessary duplication does not mean eliminating every repeated value. A relational design can enforce references and support flexible linked queries, at the cost of designing keys, constraints and queries. For a tiny independent list, that overhead may outweigh the benefit.
Identifying and finding records with keys
Keys distinguish a record's identity from its references and the fields used to find it.
Primary, foreign and secondary keys
A primary key uniquely identifies a record within its table and must not be null. A foreign key is a field or combination of fields referencing a candidate/unique key in another table, usually its primary key. A secondary key is a field used to search or select records and need not be unique.
MemberID is the Member primary key. Two different members can both be called Hana, so Name would be a poor choice. A candidate key is a possible minimal unique identifier; one is chosen as the primary key. A key need not be numeric. A composite key uses a combination of fields: if a member can attend a given workshop only once, (MemberID, WorkshopID) uniquely identifies an attendance record even though neither field alone does. The rule must hold for all permitted records, not just today's sample.
Loan.MemberID references Member.MemberID. It can repeat because a member can have many loans, and it is not Loan's primary key: LoanID uniquely distinguishes loans. A column can be both part of a composite primary key and a foreign key, as MemberID is in the attendance table.
Member.Name or Member.Postcode can serve as secondary search keys: searching a postcode may return several members. A secondary key is not automatically a foreign key or an enforced alternate unique key. An index may help a DBMS search it efficiently; indexing has its own storage and maintenance cost.
Reading one and many ends
An entity relationship diagram (ERD) models which types of entity are related and the cardinality of each relationship.
Cardinality states how many occurrences can be linked. Under a rule that each member has one card and each card belongs to one member, Member–MembershipCard is one-to-one. One Member can have many Loan records, but each Loan belongs to one Member: Member–Loan is one-to-many. If a Member can attend many Workshops and a Workshop can contain many Members, Member–Workshop is many-to-many. Read each direction separately; “there are many members” does not establish a relationship's cardinality.
[DIAGRAM: Three relationships shown using OCR entity rectangles, straight lines for one ends and three-pronged crow feet for many ends; asset_slug: entity-cardinalities; caption: The crow foot touches the many entity: Loan in Member–Loan, and both entities in Member–Workshop.]
OCR Appendix 5f represents an entity as a rectangle. A one-to-one connection is a straight line. A one-to-many connection has three prongs at the many end; a many-to-many connection has three prongs at both ends. These prongs are crow feet, not arrowheads showing data flow. Do not add optionality circles or bar symbols from a different ER convention when reproducing this notation. The pictured relationships are three separate examples, not one combined lending schema.
Continue this lesson.
You’re previewing NovaLearn without an account. Create a free account to read the rest.
Create free account