From Requirements to the Relational Model

3.1 Course Journey and the Project

3.1.1 The Journey So Far

Before going deeper into today's topic, take a moment to recap the journey this course has taken so far and what it means for you.

The starting point was simple: in the database system world, there is a requirement to store data, fetch it across, and retrieve it. Doing that with traditional programming languages like Java or Python is a very tedious task. It demands a huge effort and a lot of programming structure from our side. To store and retrieve data in a concurrent manner, we wanted a proper scheme, a proper structure. That is why we say any database management application has four essential properties:

Real-world: this is exactly what the big database products provide. The range runs from Oracle to open sources like MySQL, and Microsoft SQL Server also has these features, along with many, many more options.

Why does any client want to create an application in the first place? In real life, a person may be solving the problem of one or two, or a limited number of, people. Going into the online world and creating an application — whether it is a desktop application, an online web application, an enterprise application, or a mobile application — lets that person reach at least an order of magnitude more people. And in every case, whenever an application is required, the people involved need to discuss and deal with data. This is also where the design process starts: the textbook design workflow runs through requirements analysis, conceptual design, logical design, schema refinement (normalization), physical design, and finally application and security design. Today's class covers the middle of that journey — turning a raw requirement into a conceptual picture (the ER diagram) and then into a logical design (the relational schema).

There is also a reality check for anyone who wants to become a data scientist. Many of us think data science means AI, machine learning, transfer learning, reinforcement learning, and so on — those are the keys. However, the major important component is dealing with the primary data: which data we can rely upon, which data we cannot rely upon, and how to get that particular data. In many organizations that is very important even when the three roles are not segregated. There is a data analytics person or a team that takes the data away and uses it.

3.1.2 The Project Assignment

One important component of today's session is the project. The assignment is this: take an airline company — for example, Tansai Airlines — or any airline company, or any banking application, or a supply chain application, and so on and so forth. Give a brief description of that application: who are the users, what are the benefits, and list the functions and features, and so on and so forth. You need to be aware of the project and keep working on it side by side, because the labs will expose you to it very, very soon.

The same requirement, in a similar learning form, will be shared in today's discussion — not exactly the same, but similar. From that requirement you can easily convert to an ER diagram, and then convert the ER diagram into a relational model as you progress. Keep in mind that the project expects you to do exactly these things.

A milestone-based structure was floated for the project: starting today, submit the requirement specification by next week, the ER diagram within two weeks, and keep going from there. Submitting at each milestone helps, and feedback at each milestone would be nice — but the evaluation also has to respect the needs of every student in the class and keep everyone on the same level playing ground. That bare minimum is what the evaluation rests on. Students who want more — a new case, a new assignment, regular or detailed feedback — are welcome to ask for it.

3.1.3 Student Questions and Answers

3.2 Customer Requirement Specification

3.2.1 The Lufthansa Airlines Example

A requirement specification may come to you while you are wearing the hat of a database administrator or a designer. The user may start speaking about the problem he or she is solving. Say the user flies Lufthansa Airlines planes and is the owner of that airline. The user says: now we want to create a system where we get to know, in which part of the world the geographical geopolitics is playing out, what are the demographic trends that are happening. We want to store all the data — this is a place where the World Cup is going to happen, this is a place where Olympics are happening, this is a place where elections are going to happen, these are the demographic changes happening in that particular territory, and so on and so forth. All of them impact and affect the pricing we have as an international airline — how much price we can quote. And this needs to be planned way ahead, at least a year ahead. If something needs to be done — like the Qatar football World Cup was happening — we may have more flights diverted to that place during that time.

Very good, very good problem — it can nicely be done. The basic idea is that somebody needs a place to store the data, somebody needs to access the data, and then a data analytics person or team takes the data away. The requirement for the user starts from exactly what you just heard. Then the design team starts asking the client: okay, I understand that, but what are the different components in that particular application? Who are the players you will be solving for? What are their roles? And so on and so forth. The customer requirement specification you use to create the ER diagram — the diagram we saw in the last class — and that diagram will be used to convert into a relational schema.

Real-world: this is dynamic pricing in the airline industry, driven by geopolitics and demographics. It is exactly the kind of problem where the database designer turns ten, twenty, thirty, forty pages of requirement into a picture the client can confirm.

3.2.2 The University Schema and Pertinent Questions

The university structure we worked with in the last class shows how a first-level cut looks. The user comes to you and says: yes, we have a department, we have an instructor, we have students, and students have these things. We need to store courses, sections, some other information, classroom, and enrollment — these things. That is the first-level cut the user gives you. Now it is your responsibility to keep asking pertinent questions to get more about the user's requirement.

What kind of questions work? If you ask "what are the constraints between a section and a department?", the user might go blank — "what do you mean by requirement? what do you mean by constraints?" That question is difficult at first. A better question is: how are students and instructors related? The client can start describing that. Why do you ask? So that you can capture and understand the requirements — later you can create the ER diagram, a place where you can store and retrieve the information whenever required.

Some better questions: how are the prerequisites of a course managed? Do courses have multiple prerequisites? That is a straightforward question, and answering it uses our one-to-one, one-to-many, total participation ideas. Can multiple instructors teach a section? Is it limited to one? Can one instructor teach one? After all these questions you will know whether this requirement is many-to-many, many-to-one, or one-to-one, and you will use exactly the same thing to create an ER diagram, something similar to what you have already seen in previous classes. Because your requirements are clear — you have seen the requirements from the user — you can draw the diagram, show it to the client, and say: is there anything more or less that I need to verify? The client says yes, this is okay. Then you use that information to convert into a relational schema, which you will use to store and retrieve the information.

Remember the purpose of the ER diagram: all those ten, twenty, thirty, forty pages — or four pages — of requirement you specified are encapsulated into one picture so that everything — the constraints and the requirements — is clear. Then the diagram is used to create a relational schema, wherein we will use insert, update, create — those queries — to store and retrieve the information. Whatever SQL experience you have comes at the later stage; till then, the client wants to create an application, we are leading the database team in our organization, and we are responsible for creating the structure where the data will go and the structure of how the data will be retrieved in a possibly concurrent manner.

3.2.3 The Room Analogy

3.2.4 The End-to-End Process

The whole process, in one line: the user gives the requirement; the requirements need to be converted in such a way that we understand all of them; we convert them into a relational schema, wherein we will store and retrieve everything. So the process of converting to an ER diagram and then converting to a relational schema is equally important as storing and retrieving through SQL.

3.3 ER Model Recap: Entities, Relationships, Weak Entities

3.3.1 Entities, Attributes and Relationships

From the last class we discussed what an entity is and what a relationship is. There is a place where the user requirement is specified, from which you have converted into entities. An entity has a name; it has its own attributes. There is a relationship, and there is one-to-many or many-to-one with total participation. Wherein we have a weak entity as well.

The entity-relationship model is exactly what its name says: entities and relationships. In the diagram, the rectangles hold entities, and the rhombuses represent relationships — a relationship is never an entity, it is always a relationship, connected to one, two, or more entities. You may also think of encapsulating several entities and relationships into a single relationship that has its own attributes, so that the relationship carries proper attributes and they are defined properly.

3.3.2 Weak Entities and the Identifying Relationship

The relationship from a weak entity to its owner entity — also called the identifying entity — is drawn as a double rhombus. Whenever a relationship connects a weak entity to its owner entity, it is a double rhombus; other relationships with a weak entity are not double rhombuses. In the university schema, the owner entity of section is only one — the course. That is why the double rhombus is on that one side. Its owner entity defines its key: course ID, section ID, semester, and year. Section has only one owner entity; everything else section is associated with takes part as a normal entity. This is the way we read it across.

3.3.3 Reading Cardinality and Participation

There are arrows in some places and no arrows in others. A pointed arrow means one; without the arrow means many. The double line means total participation. For example: a student can be advised by at most one instructor, and an instructor can advise many students — this is many-to-one. When a student and a department are related: a student can be related to at most one department; a department can have many students. However, all students in the student entity set must be related to some department — that is total participation. Look at the bag of student entities and the other bag of department entities: every entity in the student bag must be related to some department. So the double line says the participation is total, and the arrow says the student can be related to at most one department.

3.3.4 Student Questions and Answers

3.4 Keys: Super Key, Key, Candidate Key, Primary Key

3.4.1 The Four Key Concepts

The keys we see underlined in the ER diagram belong to the relational schema world. There are four concepts to keep straight: super key, key, candidate key, and primary key.

Another everyday example, from the reference book: the CAR relation has two candidate keys — License_number and Engine_serial_number. Either one identifies a car; the designer underlines one of them as the primary key and keeps the other as a unique key. Notice the two keys have the same size here, but that is not required: a key is minimal, not "smallest" — one relation can have a one-attribute key and a three-attribute key at the same time.

The intuition, as the discussion emphasized: do not cram these definitions. Understand the relationship between them — the more you understand why, the more heartily and correctly you will do it every time. Think of the chain as containment: primary key is one of the candidate keys; candidate keys are exactly the minimal super keys; super keys are all unique combinations. Once you see the containment, you never need to memorize a single definition.

3.4.2 Foreign Key at a Glance

A foreign key is a different concept from the super key, and it will come up again in detail later. For example, in the advisor relation there is a student ID. The student ID refers to the attribute ID in the student relation. That is a foreign key referring to the ID attribute in the student relation. Why do we do that? Because if the student does not exist, this record should not exist as well — that is why we refer through a foreign key.

3.4.3 Student Questions and Answers

3.5 The Extended ER Model: Ternary Relationships

3.5.1 Ternary Relationships

A topic that was asked about today is the ternary relationship and things like that. Let's first talk about the extended entity-relationship model. It might not be directly used, but there can be relationships of higher degree, like the ternary model. Suppose you have a project, an instructor, and a student. You are a student and I am an instructor, and you are working on a project — that project can be anything. Hypothetically assuming the project is a very, very valuable one — it may turn out to be the one that leads to a research publication, or to commercialization, or a conference publication, or a patent. In that case, the student, the instructor, and the project are related. They automatically have some relation which cannot be further bisected into two: the student has its own entity, the project has its own entity, the instructor has its own entity. Even though we wanted to make it a binary one, the relationship is such that it has to be the project-guide relation. We cannot have it as two-one.

3.5.2 Degree versus Cardinality

There are two things to keep separate here: degree and cardinality. The degree is whether the relationship is binary, ternary, and so on — how many entities participate. Cardinality is whether the relationship is one-to-one, one-to-many, many-to-one, and so on. Now the question: when there is a ternary relationship, can we specify cardinality? You will find that even if there is one arrow, you may say many of them are participating, but generally it is two-way. How complex it goes — even to speak about it you can appreciate how difficult it is in real life. That is why we avoid more than two participants.

3.5.3 When to Use the Extended Model

Can the relationship be more than ternary? Of course, of course — if required, you can have it. Try to make it two; if not, three. But that makes it very complicated, and generally we do not experience it in normal life. As for whether the extended model comes after the original ER model was developed: no, it is simultaneous. Whenever it is required, you can form it. There are some other bags in your armour that you can use to shoot — you get some different arsenal whenever it is required. It is the same thing, the same concept: whenever you require it, use it; when you don't require it, don't use it. Don't try to overcomplicate it.

3.5.4 Categories and Union Types

The extended ER model has a second construct beyond the ternary relationship: the category, also called a union type. A category is a subclass that has multiple superclasses, where the superclasses represent different entity types. This is the precise distinction that students mix up: in specialization, the subclasses all inherit from one superclass (SECRETARY and ENGINEER are subclasses of the single superclass EMPLOYEE); in a category, the members are drawn from several unrelated entity types, and the category represents their union.

The inheritance story is different from specialization and worth stating exactly. In specialization, the subclass inherits the union of all the superclass attributes. In a category, the superclasses are disjoint, so the category cannot inherit attributes wholesale — instead, each member of the category keeps its own attributes in its own entity type, and the category contributes only the attributes that apply to every member (in the example, the owner's registration and license details). Attribute mapping to the relational schema follows the same logic: the category becomes a relation whose primary key is the union of the keys of the member superclasses — for OWNER, the SSN of a person owner, the bank code of a bank owner, and the registration number of a company owner — or, in many designs, a single surrogate owner ID that the application guarantees unique across all three member types. Each member superclass relation keeps its own key as its primary key and refers to the category's key as a foreign key.

The practical rule the designer needs: use a category when a requirement says "any one of these different kinds of things" — a vehicle can be owned by a person, bank, or company; a property can be owned or rented by a person or an organization; an insured item can be a house, a car, or a person. Use specialization when the requirement says "this one kind of thing, divided into kinds" — an employee that is a secretary or an engineer. Category answers union; specialization answers partition.

3.5.5 Student Questions and Answers

3.6 From ER Diagram to Relational Schema

3.6.1 Entity to Relation

Now that we know how the ER model looks, the next step is converting the ER model to the relational model. If this is an entity, the entity is directly converted into a relation. When we say a relation, we mean the relational model's relation — which somebody may also signify as a table. The entity name becomes the relation name — the department entity becomes the department relation. What are the attributes? The same ones. Which one becomes the primary key or the candidate key? The same one that was the key in the ER diagram. Directly, no problem. Similarly, all of them can easily be converted.

3.6.2 Multivalued Attributes

The only thing to remember is the multivalued attribute. For example, day, start date, and end date of a time slot are multivalued. You have two options. Option one: have them as part of the key — time slot, day, start date, and end date together become the primary key. Option two: keep the primary key as is and create a separate relation to store them if they are multivalued.

3.6.3 Relationship to Relation

When we have a relationship to convert, the relationship will have the keys of both sides as its attributes — the key here and the key there. Then we convert it into a relation, because that is how we capture that a student is related to a department: how else do we store the relation? Where we store its keys, which are part of it. What becomes the primary key of that relation is decided by us — by reasoning, not by cramming.

What about attributes with null values? Don't worry about null values here: except for the key, everything can be null. Everything is allowed to be null except the key.

One clarification that came up repeatedly: the entity section and the relation section course have the same attributes, and sec class and sec time slot are not part of the takes relation. Why? Because the owner entity of section is only one — the course. That is why the double rhombus is on that one side. Its owner entity defines its primary key: course ID, section ID, semester, and year. After that, section is a unique entity in itself. So when the takes relationship is there with section, section has its key — course ID, section ID, semester, year — and that's it. Other entities section is associated with take part as normal entities, not as owner entities. Section has only one owner entity which defines its key.

3.6.4 Reducing Redundancy

Now the discussion that followed: the student-department relation and the student relation. There are two things we need to avoid. One: there should be the least amount of redundancy — that means if the same thing is stored twice, we are misusing the storage. Two: what about consistency? If I am storing the student ID and the student name in the student table, and there is another table in which I am storing the student ID and name as well, then at one point of time the student decides to change the name. Suppose my name was Amit Dua and I decide to change it to Amit Kumar Dua. Now if there is redundancy, it may be possible that the two different tables tell two different things — who will you believe in that particular case? We need to ensure that every time we update, everything in the redundancy is updated; if anything is off, there is chaos, we cannot believe anything. So redundancy has more to do with consistency — with the certainty of what we derive.

In this case: all the students are stored in the student table. The student-department relation has an ID and a department name. Because a student can be related to at most one department, and every student is related to some department, all the students in the student table will also appear in this particular table — with exactly one entry each. So there is redundancy: something extra is being stored. Tomorrow, if the student's ID gets changed — it happens; students sometimes have a vertical or a horizontal transfer. Initially a student was assigned to computer science, and later decides to do mechanical; by the dint of very good grades a student can apply and transfer the stream. Or a student joined the campus premise on an MSc program — physics, chemistry, biology, mathematics, or economics — and based on the performance of the first year, they are given the basic graduation course, civil, chemical, mechanical, computer, or electrical. In the second year their ID changes every time: initially they had something like 2023 B4 PS, where B4 means mathematics; later they become B4 A7, where A7 means computer science, with their ID numbers changed. Even an employee's ID number, let's say 1234, can change for whatever reasons. Now the same ID number is stored in the student table and in the other table as well. There is a redundancy, and that may lead to unnecessary storage space — at least in this particular case, not a consistency violation. I have to at least change two places whenever I am related, without any particular saving. So I can easily make a single table out of it — a student-department table — just to ensure there is the least amount of redundancy. Somebody may say whatever normalization will come across will fix it, but primarily from the redundancy aspect we can still reduce the relational schema.

Similarly for section-course: everything in section is in section-course as well — all the attributes are the same — so it does not make sense to have two tables. By default, we have just come across: wherever there is a many-to-one relation where the entity on the many side is participating, we can reduce two relations into one.

3.6.5 Student Questions and Answers

3.7 The Relational Model

3.7.1 Everything Is a Relation

The relational model is the one we have just converted the ER diagram into. The basic premise is that everything is a relation. That's it. This is inspired from Edgar F. Codd's paper — and because of this paper, he was awarded the Turing award. It is a very fundamental paper, so understand the beauty of it. People were trying many different things earlier — the network model, or some other model. Codd gave a very simple understanding, which was really very fundamental. In the 1970s some principle was formed, and till today — 60, 50 years later — the same principles are used. That is why he was awarded the Turing award: that is the beauty of classic fundamental principles — the applications and advancements keep on happening on top of them.

And the situation is like the house again: where do I store my books, where do I store my bedsheets, where do I store my entire clothes, where do I store my keys, where do I store my daily course items in the house, and how do I store everything else? That was the problem in the 1970s — and still now, in the real life of many people — analogous to the problem with data. In the relational schema, everything is a relation, and the relation looks like a table with rows. That is how it will be stored.

3.7.2 Relations, Tuples, Domains and First Normal Form

The relation name is student; it has different attributes; these are different tuples; and that is how we denote it across. The domain of a relation is inspired from the fact that we are using first normal form. First normal form is the normal form where we say every entry in a particular tuple's attribute is unique — I cannot have multiple entries here. By default it is minimal and it is unique. That is what we are trying to work upon.

3.7.3 Schema versus State

The table definition — how we are defining the table — is the schema of the relation. When we say student has an ID and a name, whatever we write across, that is the schema. And once the schema is populated, it is the state of the relation — with the values being populated. So the state is where not only we are defining the attributes but also the values that are stored.

3.7.4 Table versus Relation

There is a difference between a table and a relation, and we need to understand it. Although we say a relation is represented like a table, in a table we can have repetitions and there is an ordering. But in a relation we don't have ordering — neither across tuples nor within attributes — and we don't accept repetitions either. Once they are defined, of course, we need to respect how they are stored properly. But the relation itself has no ordering, and no two records can be the same, because it is a set.

3.7.5 Student Questions and Answers

3.8 Relational Constraints

3.8.1 Three Kinds of Constraints

The different constraints are very important to discuss — take it as a very important thing. Some of them are implicit constraints: for example, we need to store only unique values in every attribute, or every attribute of a tuple that I consider — it is unique, we cannot store two things. That is an inherent constraint that the schema presents to us. Some of them are explicit, schema-based constraints: this is a primary key, this is a non-null attribute, something like that. Some of them are application-based: those will have to be handled at the programming side. For example, you may want to ensure that the salary of a manager has to be higher than the salary of his or her subordinate — the subordinate's salary should be lesser than the manager's salary. Or, in this particular world, whenever somebody graduates, his or her salary should be higher than the professor's salary — in a fantasy world. Something can happen depending upon that requirement; that is an application-side requirement.

3.8.2 Integrity Constraints

Then there are the relational integrity constraints. There are key constraints — constraints on the side of something related to an entity. Primary key, secondary key, candidate key — these are also called entity integrity constraints. Some of the constraints are referential integrity constraints, and some are domain constraints: I need to ensure that there is no null; I also need to ensure that an attribute can only store integers, or only strings, if the domain is like that. For example, if this is an SSN number, and the domain is integer or the format is like that, I need to respect that — I cannot store the SSN number as "Amitua". That is another constraint that I need to respect here.

For the domain: some constraints are related to a super key or a key — we already know how to apply those. Some constraints also say how to read the constraint for the key.

3.8.3 Reading a Key Constraint

Let's suppose the key is engine serial number and make together — hypothetically assuming, assuming, assuming, all of you. In that particular case, what do I mean by saying this set of them will always be unique? This is what I am saying: anything is unique. Because it is a relation, no two particular records can be the same. And also, the order does not matter — license number, serial number, engine number, make, model: there is no ordering either across different tuples or within different attributes. Once they are defined, we need to respect how they are stored properly, but the ordering is not part of the relation.

3.8.4 Student Questions and Answers

3.9 The Foreign Key Constraint in Detail

3.9.1 The Works On Example

The very important thing coming across is the foreign key constraint. In the company schema, the employee relation has SSN as the primary key, and the department relation has the department name as the primary key. There is also a works_on relation. When we write the works_on relation, the project number here references the attribute P number in the project relation. Why do I say references? The foreign key in this particular relation is the P number, which is referencing the P number in the project relation. Because of the fact that I want to store here the P number only for the projects which exist, and also want to keep a constraint that whenever somebody deletes the P number from the project, either that can be deleted, or I restrict that, or I store a default value there.

CREATE TABLE WORKS_ON (
    Essn  CHAR(9)       NOT NULL,
    Pno   INT           NOT NULL,
    Hours DECIMAL(3,1),
    PRIMARY KEY (Essn, Pno),
    FOREIGN KEY (Essn) REFERENCES EMPLOYEE (Ssn) ON DELETE CASCADE,
    FOREIGN KEY (Pno)  REFERENCES PROJECT  (Pnumber) ON DELETE CASCADE
);

3.9.2 Delete Options: Restrict, Cascade, Set Default

So when a record in the project table that has records in works_on is to be deleted, we have three options: either we should not allow it — restrict; or we have to cascade — delete the referencing entries too; or we choose a default value. The default value must exist in the referenced table — otherwise the foreign key will not be valid. For example, if a manager leaves and the CEO is the manager, you can think of it here: that default exists, and the foreign key will hold. If it is not there, then the foreign key will not hold.

3.9.3 Self-Referencing Foreign Keys

Sometimes the foreign key refers to the same relation as well. You can relate to it: the employee relation has SSN and super SSN — the super SSN is a foreign key referring to the SSN of the same relation. If I am storing my data, I am also storing within my data my manager's data: super SSN is the manager's SSN — who is managing me. But it is important that the manager must also be an employee — that's why the information is different in the column. It is my information; it is my manager's information; but the manager also has to be an employee. There might be a manager table, or there might not be — in this case, in this table only, we are storing who is my manager. It is a way to tell that there is a foreign key which can refer to the same relation. Similarly, you may think of a D number referring to the department relation, or a manager SSN, and so on and so forth. Sometimes in the relational schema we not only represent the relation and the attributes, but also can represent the foreign keys as well.

3.9.4 Where Foreign Keys Come From

In the relational world there is no concept of an entity in itself: this is a relation, and in that relation there are attributes, and these attributes have foreign keys that we denote here. How did the foreign key, which was not in the ER model, come across? The foreign key may come across when we are merging two relations. One of the ways in which a foreign key appears: we have an ER diagram — entity, relationship, another entity — and we formed a three-relation conversion, and we decided that now two of them will become a part of each other. Then we will say: yes, since we are combining them, we will have a foreign key from one of the attributes to the other entity which was there. That is how we denote it across. Even if there is a relationship whose relation or table we are converting in the relational schema, that itself is referring to the primary keys of the individual attributes. If you look at the advisor relation, the relationship between a student and an instructor: in that advisor relation, it has a student ID and an instructor ID. The student ID is referring to the student's relation, and the instructor ID is referring to the instructor relation. Both of them are primary keys there, and both are foreign keys here.

3.9.5 Student Questions and Answers

3.10 Relational versus NoSQL and Document Databases

3.10.1 The Postgres JSON Question

Generally we learn in the relational model that the attribute should have only one value — we cannot store multiple values in the same attribute. But practically, in one implementation in Postgres in some organizations, there is a data type called JSON, which is given as one of the fields, and the organization actually stores a set of key-value pairs in the same attribute. Is this an exception, or is the implementation itself something different?

The response: first of all, a confession — I have not worked on Postgres. Secondly, there are different types of storage as well. What we are showing is the relational one. However, the key-value pair is a document database, generally — in a document database like Cassandra, we have a key-value pair, which is a NoSQL relation. NoSQL means the constraints for the relational model do not apply in that world. They generally use JSON, or binary BSON — the binary version of JSON — which is MongoDB, another version of that. So the constraints we are discussing here in the relational one would be differently applied in that one.

{
  "ID": "22222",
  "name": { "firstname": "Albert", "lastname": "Einstein" },
  "deptname": "Physics",
  "children": [ { "firstname": "Hans", "lastname": "Einstein" } ]
}

3.10.2 Document Databases and the CAP Properties

Here we want to ensure that all the ACID properties — everything — is followed. (The spoken wording in the lecture came out as "asset properties"; the standard term for the relational transaction guarantees is ACID — atomicity, consistency, isolation, durability.) There, they talk only about either of the consistency, availability, and partition tolerance — the CAP properties — (the spoken wording was "CAS property"; the standard trade-off triangle for distributed databases is CAP — consistency, availability, partition tolerance) because in that world we talk only about the scalability aspect. Many industries are using some form of NoSQL because of the scalability aspects and other aspects. If you understand this particular purpose — how you store, how you retrieve — the small adjustments you have to do will be taught in this particular course later on, most likely if we have time, so that you can also understand this world as well.

3.10.3 The XML Data Model: XSD Constructs

Between the rigid relational world of this session and the flexible document world of NoSQL sits a third, older semi-structured data model that the syllabus explicitly includes: XML (the Extensible Markup Language). XML documents are self-describing — the data carries its own structure as nested tags — which is why XML became the standard interchange format for industry: the same document can move between systems that share nothing but the format. An XML document is a tree: a root element, child elements, text content inside elements, and attributes attached to elements.

The schema of an XML document is defined with XSD — the XML Schema Definition language — and four constructs carry almost all of its expressive power:

• xsd:element declares an element. The element gets a name and a type; the type may be a built-in simple type (xsd:string, xsd:integer, xsd:date) or a user-defined type.
• xsd:complexType declares an element that has structure — child elements and/or attributes — as opposed to a simpleType element that holds only text. A complexType is the XML analog of a relation or a record: one element that contains several others.
• xsd:sequence is the compositor inside a complexType that fixes the order of the child elements: the children must appear in exactly the declared order. (The alternatives xsd:choice — exactly one of several — and xsd:all — any order, each at most once — complete the compositor family.)
• minOccurs and maxOccurs set the cardinality of a child element inside a complexType — the exact same (min, max) idea as the ER multiplicity notation of the earlier sessions. The defaults are 1 and 1 (exactly one, mandatory). minOccurs="0" makes the child optional; maxOccurs="unbounded" makes it a collection — the XML way of expressing "many."

A worked schema makes all four constructs land at once. The requirement: a course catalog in which a department offers zero or more courses, each course has a number, a title, and one or more instructors:

<xsd:element name="course">
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="number"   type="xsd:string"/>
      <xsd:element name="title"    type="xsd:string"/>
      <xsd:element name="instructor" type="xsd:string"
                   minOccurs="1" maxOccurs="unbounded"/>
    </xsd:sequence>
    <xsd:attribute name="code" type="xsd:string"/>
  </xsd:complexType>
</xsd:element>

Read against the ER vocabulary: course is the entity; number, title, instructor are its attributes; the instructor element with minOccurs="1" maxOccurs="unbounded" is a multi-valued attribute with total participation — every course has at least one instructor, possibly many; and code is an XML attribute (metadata about the element) rather than a child. Every constraint of the earlier ER lectures — single-valued versus multi-valued, at least versus at most — has an XSD spelling. And the same reading transfers in the other direction: xsd:choice between two elements says "exactly one of these alternatives, like a discriminating attribute," and xsd:all says "these children may come in any order," which a relational table would never care about at all.

3.10.4 XML and Database Integration: Native XML vs Publishing

The syllabus's second XML topic is the two-way bridge between XML and relational databases: when the data lives in relations, how does it get out as XML — and when XML arrives, where does it live?

Publishing XML from relational databases. When the relational data is the source of truth, XML is produced on demand as an interchange format: a query extracts the tuples, and the engine formats them into a well-formed document. The mapping is the mirror of the ER-to-relational mapping of section 3.6: each relation becomes a complex element; each tuple becomes a child element under it; each attribute becomes either a child element or an XML attribute; and each foreign key reference becomes a nested child — the relational hierarchy is the XML hierarchy. Standard SQL has built-in support: the XML data type plus the XMLElement, XMLAttributes, and XMLAgg functions (the SQL/XML standard) compose relational results into documents, and XSLT transforms them into any presentation format. The advantages are total: the data keeps full relational query power, joins, transactions, and integrity; XML is produced only when something asks for it. The price is that a document spanning several joins must be assembled each time it is requested.

Native XML databases. When the document itself is the unit of work, the document is stored as-is in a native XML database — no table decomposition, no shredding into tuples; the engine stores the tree (often in B+-tree-indexed nodes, connecting to the indexing story of later sessions) and answers queries in XQuery, the query language for XML documents. Native XML preserves document order, comments, and structure exactly, and the document is stored and retrieved atomically — like a BLOB with structure. The price is that joins, integrity constraints, and the transactional guarantees of the relational model are weaker or absent, exactly the trade-off the session described for NoSQL documents. The practical rule: native XML when documents arrive whole and leave whole and their internal structure matters (medical records, legal filings, product feeds, configuration catalogs); publishing when XML is only a delivery format and the authoritative data must stay queryable as relations.

The connection to the session's own Postgres-JSON discussion is direct: JSON, BSON, and XML are three dialects of the same semi-structured idea, and the relational-vs-document tension described in 3.10.1–3.10.2 applies identically to XML — it was, in fact, the industry's first experience of that tension, a decade before NoSQL.

Real-world: XML is the interchange standard in finance (FIX, ISO 20022 messages), publishing (RSS feeds, DocBook), and government data exchange; SQL Server and Oracle both ship the XML data type with XQuery support, so the SQL/XML mapping above runs in production databases today.

3.10.5 Student Questions and Answers

Exam Guidance Summary

The session's exam-relevant points, gathered in one place:

• The project and assignment are marked and carry weightage. At least basic feedback is expected; if feedback is missing along with the marks, you can reach out and arrange a one-to-one discussion of your assignment.
• The first quiz window runs from the 19th to the 28th of February — that is the first assessment deadline for the course, and note that it is a quiz, not the assignment. Keep it in mind if you have any questions about scheduling.
• The project is milestone-based in spirit: the expectation is a requirement specification first, then the ER diagram, then the relational model, then normalization and validation — and you may convert the same requirement into an ER diagram and then into a relational model as you progress. Submission at each milestone, with feedback at each milestone, is the preferred structure.
• Do not cram the ER-to-relational conversion rules: the primary key choice in a relationship relation follows from reasoning about the cardinality — which side can appear at most once. In a one-to-many relationship, the key is the many side; in a many-to-many, both keys together form the key. Understand why, and you will do it correctly every time.
• The four key concepts — super key, key, candidate key, and primary key — behave like a containment chain: the primary key is one of the candidate keys, the candidate keys are exactly the minimal super keys, and every super key is any unique combination. Do not cram the definitions; reason about the containment.
• The relational model fundamentals, including the domain of a relation, are the core of today's material — review the earlier session's material for the formal definitions, since today's session focuses on the practical application of the relational model.
• The different constraints — key constraints (entity integrity), referential integrity (foreign keys), domain constraints, and not-null constraints — plus the three delete options for foreign keys (restrict, cascade, set default; standard SQL adds set null) are the important conceptual pieces of this session.
• The distinction between degree and cardinality (3.5) is a frequent source of confusion: degree counts the participating entity types; cardinality describes one-to-one, one-to-many, many-to-one. In a ternary relationship, cardinality becomes genuinely ambiguous — which is exactly why designers avoid more than two participants.
• Labs are coming very soon and will expose you to the project work directly; keep the project going side by side.

Key Industry Applications

How today's session plays out in real organizations:

• Real-world: database management systems from Oracle to open sources like MySQL, and Microsoft SQL Server, all provide the four essential properties — data storage, abstraction, views, and concurrent sharing.
• Real-world: dynamic airline pricing at an international airline like Lufthansa — using geopolitics, demographics, World Cup, Olympics, and election data to plan pricing a year ahead — is a requirement-specification problem for database designers. The requirement talk you heard in 3.2 is exactly how such systems begin.
• Real-world: data scientists in industry spend a major part of their effort on the primary data — which data to rely upon and how to get it — not just on AI and ML models.
• Real-world: data as the new oil — using stored data to give customers a customized, tailor-made, "wow" experience instead of treating a product like a commodity.
• Real-world: ER diagrams are drawn with pen and paper, draw.io, or PowerPoint in practice; MindMap was used only as a teaching aid, not as a modeling standard.
• Real-world: generative AI tools (like GPT) are used in coursework and industry — use them to make tasks easier and to learn, but never to offload the thinking; AI-generated plagiarism is still plagiarism.
• Real-world: SQLite and other open-source SQL engines are interchangeable for course projects — "mostly all of them are the same."
• Real-world: government and public-sector models — for example, an employee who works in one school and also in another school on deputation — show that the requirement specification drives the diagram; the same model changes with different requirements.
• Real-world: NoSQL and document databases — Cassandra (key-value), MongoDB (BSON), and graph stores like Neo4j — relax the relational constraints in exchange for scalability; relational systems follow ACID properties while distributed systems talk about the CAP properties (consistency, availability, partition tolerance).
• Real-world: Postgres offers a JSON data type, which stores key-value pairs inside a single attribute — an example of how implementations blend worlds; understand the process and fundamentals, and you can learn any storage model quickly.
• Real-world: XML remains the interchange standard in finance (FIX, ISO 20022 messages), publishing (RSS feeds, DocBook), and government data exchange; SQL Server and Oracle ship the XML data type with XQuery support, so the SQL/XML publishing path runs in production databases today.