The goal: every member of the study group should understand the concepts of key and foreign key and how tables are connected to each other in relational databases. Consider the solutions for the cases before the session, discuss during the session.
Let's consider a simplified database for a gardening company. The company has many gardeners that take care of other people's gardens. Each garden has been divided into sections. A sectionNumber identifies each section within a garden. A gardener in charge has been assigned for each section. Other gardeners may assist him in taking care of his section. Each responsible gardener prepares a report of the conditions in his sections. The report describes the section's type of soil, its dampness, light conditions, etc. Gardeners report on the new plantings they have made. They may plant seedlings or sow seeds. In addition to the way of planting and its date, they register whether the planting was succesfull or not. The management wants to know what kind of plantings each gardener has made. This information is used, for example, in selecting the gardeners to be rewarded in the yearly Garden Festivals. The database contains basic facts about plants like the gategory of plant (tree, bush, flower, vegetable,..), growing conditions, time of blooming, color of flowers, time of harvest, etc.
Gardener( GardenerID, Name)
Garden( GardenID, OwnerName, Location)
Section ( GardenID, SectionNumber, Soil, Dampness, Lighting,
PositionInGarden, GardenerInCharge)
Reward( Year, Place, GardenerRewarded)
PlantType( TypeId, FinnishName, LatinName, Gategory,
GrowingConditions, FlowerColor, BloomingTime, Comments)
Planting ( DateOfPlanting, PositionWithinSection, WayOfPlanting,
Succesfulness, DateControlled, WhoPlanted, WhoControlled, PlantType,
GardenID, SectionNumber)
The location of a garden is presented as the identifier of a check in the map. The same check may contain many gardens.
An access control system relies on personal key cards. Nobody may pass a gate without providing his card for control. Each control produces a control record that is stored in the database. Both successful controls and failures are registered. In case of failures the reason for the failure is also registered. The reason may be, for example, a missing authorization or a failure in reading the card identifier. Tables that are used in the system include:
Person (personal_id, name, address, jobTitle)
Key_card (card_id, date_admitted, personal_id)
Control_record (card_id, time_when_controlled, gate_id, success,
reason_of_failure)
Gate (gate_id, location_of_gate)
Authorization (gate_id, card_id, when_started, when_ends,
type_of_permission)