Published on October 20, 2013
Relational database fundamentals
A relational database is a database Relational database that has a collection of tables of data items and links based on relationships. them, defined
Entity A database contains one or more related tables. Each table holds all of the information about an entity(object, person or thing.) Car Reservation House Entity Teacher Film
Attribute ▪ an entity may be a person, place, thing or concept about which data can be collected. ▪ Each entity is made up of a number of 'attributes' which represent that entity. Example Car (entity): - Brand - Model - Serial - Number
Tuple ▪ it's an ordered collection of elements in a row of table Column brand Number 9 RL 4774 DH 12 Renault Megane RL 7457 PS 12 Renault Row Serial Renault Car Model Symbole RL 8596 LL 45 Peugeot 307 KID 9875 LN 78 Peugeot 607 Chorus 3655 PR 45 Ford Ranger Match 8559 Fd 44 KIA Rio Drop 2658 GB 85
Relationships ▪ The link between entities is known as "Relationship" ▪ Relationships relational on database enable users to retrieve and combine data from one or more tables.
Relationships ▪ CAR ▪ Rent ▪ Consumer Consumer makes Rent Rent consists of cars There is two links between: -Consumer and Rent (Makes). -Rent and Car(consists) Makes and consists is known as "Relationship" Rent Makes Consumer Consists Car
Relationships Forms These relationships take the form of: ▪ One-to-one (1-1) ▪ One-to-many (1-n) ▪ Many-to-many (n-n)
Relationships Forms One_to_one: ▪ Think about a husband and wife: ▪ A husband can only have one wife ▪ A wife can only have one husband ▪ this would be known as a 'one-toone relationship' Husband Wife
Relationships Forms One_to_many: Think about a mother and her children: ▪ A mother can have many children ▪ A child can have only one mother Mother Children
Relationships Forms Many_to_many Think about videos in teacher and student ▪ A student can be taught by many teachers ▪ A teacher can teach many students Teacher Student
Constraints are part of a database schema definition. They define certain properties that data in a database must comply with. They can apply to a column, a whole table, more than one table or an entire schema. Constraints These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.
NOT NULL Constraint: Ensures that a column cannot have NULL value. Example Entity : employee Attribute: EMERGENCY_PHONE Constraint: NOT NULL
DEFAULT Constraint Provides a default value for a column when none is specified Example Entity : employee Attribute: Salary Constraint: DEFAULT 5000
Unique Constraint Ensures that all values in a column are different. Department Department number Name 01 Computer Sciences 02 Mathematic Entity : Department 03 Literature Attribute: Department 04 Religions(1) 04 Religions(2) 05 Human sciences Example Number Constraint: Unique
Primary key Constraint Uniquely identified each rows/records in a database table. It must be unique, and must have the NOT NUL Example Entity : Teacher Attribute: Id Constraint: Primary key Teacher Name Id Sadok Sassi 00098585 Rima Ben Salah 45454500 Mohamed Ouerteni 12545454 Eric Jakson 45454555 Omar Waleed 87787822
Foreign key Constraint Department Example Entity : Teacher Attribute: Department Name 01 Computer Sciences 02 enable you to define required relationships between and within tables.. Department number Mathematic 03 Literature Teacher Name Department number Sadok Sassi 01 Eric Jakson 02 Omar Waleed 03 number Constraint: Foreign key Sadok Sassi 55
Check Constraint The CHECK constraint is used to limit the value range that can be placed in a column.. Example Entity : Student Attribute: Note Constraint: check (Note>0 and Note<20)
▪ Indexes allow the database application to find data fast; without reading the whole table. ▪ An index can be created in a table to find data more quickly and efficiently. ▪ The users cannot see the indexes, they are just used to speed up searches/queries. ▪ Indexes are used by the database Index manager to: o Improve performance. o Ensure uniqueness (A table with a unique index cannot have rows with identical keys.)
Index Types of index BirthDt_teacher Non-unique indexes Birthdate They are not used to enforce constraints on the tables with which they are associated. Instead, non-unique indexes are used solely to improve query performance by maintaining a sorted order of data values that are used frequently. 02/12/1988 04/12/1986 12/04/1982 12/04/1982 Example 18/09/1981 Index name: BirthDt_teacher 14/12/1981 Table: Teacher 23/11/1980 Attribute: BirthDate 08/05/1979 Order: descendant 06/04/1976 CREATE INDEX BirthDt_teacher ON Teacher (Desc BirthDate) 23/05/1974 13/11/1969
Index Types of index UniqueName Unique indexes Name ▪ Unique indexes guarantee Management that no two rows of a table have duplicate values in the key column (or columns). Example : Human sciences Index name: UniqueName Religion (2) Table: Department Computer Sciences Attribute: Name Biology 14/12/1981 Mathematics Religion (1) Geology Create unique index UniqueName ON Department (name); Sociology Economy Laws
▪ It’s a SQL statement that is stored in the database with an associated name. ▪ A view is actually a composition of a table in the form of a predefined SQL query. ▪ A view can contain all rows of a table or select rows from a table Views ▪ A view can be created from one or many ▪ Views, which are kind of virtual tables, allow users to restrict access to the data summarize and structure data
Example of view : from table. Table Teacher with records Teacher Name Surname id Born date Nationality Sassi Sadok 00098585 02/12/1988 Tunisian Ben Salah Rima 45454500 12/04/1982 Tunisian Ouerteni Mohamed 12545454 18/09/1981 Moroccan Jakson Eric 45454555 14/12/1981 American Waleed Omar 87787822 23/11/1980 Egyptian Teacher_View Name Surname Nationality Sassi Sadok Tunisian Ben Salah Rima Tunisian Ouerteni Mohamed Moroccan Jakson Eric American Waleed Omar Egyptian From table we will create a view to be used to have Teacher , and CREATE VIEW Teacher_View AS SELECT name, surname , nationality FROM Teacher;
Welcome. Hello, my name is Adam Wilbert and I'd like to welcome you to Relational Database Fundamentals. This course is all about getting started storing ...
Chapter 1 Relational Database Fundamentals In This Chapter Organizing information Defining database Defining DBMS Comparing database models Defining ...
Fundamentals of Relational Database Design Presented by: Paul Litwin Paul Litwin is an independent developer, editor, writer, and educator. He’s the
In this module you'll learn about normalization, referential integrity, and constraints. [01:42] - Normalization[17:30] - Referential Integrity ...
Fundamentals of Relational Database Management Systems. Authors: Dr. S. Sumathi, S. Esakkirajan
Overview. Database design theory is a topic that many people avoid learning for lack of time. Many others attempt to learn it, but give up because of the ...
A relational database is a digital database whose organization is based on the relational model of data, as proposed by E.F. Codd in 1970.  The various ...
Relational database basics, including tables, fields, and relationships. Example table layouts.
This module defines databases, provides examples of relational database tables, and introduces common database terminology. [05:15] - Introduce ...
Studies in Computational Intelligence 47 Fundamentals of Relational Database Management Systems Bearbeitet von S Sumathi, S Esakkirajan 1. Auflage 2007.