Relational database- Fundamentals

50 %
50 %
Information about Relational database- Fundamentals

Published on October 20, 2013

Author: MedHEDHLY



Basics of the relational database

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;

Add a comment

Related presentations

Related pages

Relational Database Fundamentals |

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 ...
Read more

Chapter 1 Relational Database Fundamentals

Chapter 1 Relational Database Fundamentals In This Chapter Organizing information Defining database Defining DBMS Comparing database models Defining ...
Read more

Fundamentals of Relational Database Design

Fundamentals of Relational Database Design Presented by: Paul Litwin Paul Litwin is an independent developer, editor, writer, and educator. He’s the
Read more

Database Fundamentals: (02) Relational Concepts | Database ...

In this module you'll learn about normalization, referential integrity, and constraints. [01:42] - Normalization[17:30] - Referential Integrity ...
Read more

Fundamentals of Relational Database Management Systems ...

Fundamentals of Relational Database Management Systems. Authors: Dr. S. Sumathi, S. Esakkirajan
Read more

Fundamentals of Relational Database Design --

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 ...
Read more

Relational database - Wikipedia, the free encyclopedia

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. [1] The various ...
Read more

Fundamentals of Relational Databases - YouTube

Relational database basics, including tables, fields, and relationships. Example table layouts.
Read more

Database Fundamentals: (01) Introducing Core Database ...

This module defines databases, provides examples of relational database tables, and introduces common database terminology. [05:15] - Introduce ...
Read more

Fundamentals of Relational Database Management Systems ...

Studies in Computational Intelligence 47 Fundamentals of Relational Database Management Systems Bearbeitet von S Sumathi, S Esakkirajan 1. Auflage 2007.
Read more