Hi. Last session, we learned how to start from user requirements and entity relationship model, as a process of conceptual design. Now, we will learn the basic concepts of a relational data model proposed by Edward Codd, which is used during the logical design. The relational model was proposed by Edward Codd to implement physical independency, which means that in case of changes on the physical storage of the data, the SQL program shouldn't be affected, and to implement the logical independency, which means that changes on the logical schema, shouldn't affect programs and vice-versa. In order to implement the logical and physical independence of data within a relational database management system, the architecture of such DBMS must comply with the three levels of abstraction of a relational database. External level, where end users have different views of the data they use, depending on the activities they carry out with data. The conceptual level, where the data analysts have a different perception of data, they know the entire structure and relation, conceptual schema, logical structure of data and the entity-relational diagram we have obtained from the conceptual design. Physical level, in this level, database administrators have a more physical view, in terms of file system on disk, indexes etc. The following image shows the different perceptions from these three levels. Well, now we need to understand the principal elements of the relational model. On the first place, we have an attribute, which is a characteristic or property of an entity of the real world. A tuple is a set of attributes that characterize an entity. A relation is a set of tuples that represents the set of entities of the real work. The cardinality of relation is the number of tuples in relation. The degree of a relation is a number of attributes of the relation. A primary key is one or more attributes that uniquely identify the tuple. A foreign key is an attribute that allows associating to relations. The datatype is a domain of values defined for each attribute. A schema is a structural definition of relations, their attributes, datatypes and how they are associated. A database instance is the content of the sets of relations within a specific time, while intension is a set of relational schemes. A relation is made of two parts. On the first place, tuples. A set for entities of real world represented west in the relation, and a schema which specifies name of relation name and type of each attribute. A relational database is a set of relations. For example, the relation students represent a set of students, each student has a name, social security number, a phone, an address, age, and the grade point average. The schema of the relation is: Student with an sid which is any string, name, string, login which is any string, age as an integer, and gpa which should be a real. Can't think of a relation of a setups tuples? For example, as each tuple represent an entity, all tuples are distinct. In order to preserve data integrity within relational model, there are constraints. Domain constraint, the value for each attribute must be an atomic value of its domain. If it was defined as an integer, you should take one value from the set of all possible integral values. Uniqueness, a primary key cannot be repeated. An entity integrity by primary key, a primary key must be unique and not null. Semantic integrity with user-defined constraints, the value of the attributes must correspond to the business rules. A salary cannot be negative for example. Referential integrity with a reference constraint, the value of a foreign key must previously exist as a primary key value in the parent relation. For instance, in the relational model, all students enrolled in courses are represented in Enrolled relation. Therefore, there are three relations: Students, Courses and Enrolled. The primary key in Enrolled shall be composed by each primary key of the relation is associating, the primary key of students and the primary key of courses. At the same time, the social security number, SSN, in enrolled must take any value from students, as foreign key, and cid in enrolled must take any value from courses, as foreign key. The primary key in student's social security number is a foreign key in Enrolled referring to students. In this case, students with ssn, name, login, age, and gpa. Courses with cid and namecourse. Enrolled with ssn and cid as the composed primary key and grade. If all foreign keys constraints are enforced, referential integrity is achieved. How to enforce referential integrity. Consider students and Enrolled, the social security number in Enrolled is a foreign key that references students. What should be done if an enrolled tuple with non-existent student id is inserted? Reject it. What should be done if a student's tuple is deleted? Also delete all Enrolled tuples that refer to it. Disallow deletion of a Student tuple that is referred to. Set social security number in Enrolled tuples that refer to a special value null, denoting 'unknown' or 'inapplicable'. Similar if primary key of Students tuple is updated.
