SQL and Relational Model: Understanding Data Definition and Relationships using SQL, Slides of Database Management Systems (DBMS)

Download SQL and Relational Model: Understanding Data Definition and Relationships using SQL and more Slides Database Management Systems (DBMS) in PDF only on Docsity! SQL Data Definition Docsity.com SQL • Originally ‘Sequel’ - Structured English query Language, part of an IBM project in the 70’s • Sequel was already taken, so it became SQL - Structured Query Language • ANSI Standards • SQL-89 • SQL-92 (SQL2) • SQL-99 (SQL3) • Most modern DBMS use a variety of SQL • Most based on SQL2, increasingly SQL3 • Few (if any) are true to the standard Docsity.com Non-Procedural Programming • SQL is a declarative (non-procedural) language • Procedural - say exactly what the computer has to do • Non-procedural – describe the required result (not the way to compute it) • Example: Given a database with tables • Student with attributes ID, Name, Address • Module with attributes Code, Title • Enrolment with attributes ID, Code • Get a list of students who take the module ‘Database Systems’ Docsity.com Procedural Programming Set M to be the first Module Record /* Find module code for */ Code = ‘’ /* ‘Database Systems’ */ While (M is not null) and (Code = ‘’) If (M.Title = ‘Database Systems’) Then Code = M.Code Set M to be the next Module Record Set NAMES to be empty /* A list of student names */ Set S to be the first Student Record While S is not null /* For each student... */ Set E to be the first Enrolment Record While E is not null /* For each enrolment... */ If (E.ID = S.ID) And /* If this student is */ (E.Code = Code) Then /* enrolled in DB Systems */ NAMES = NAMES + S.NAME /* add them to the list */ Set E to be the next Enrolment Record Set S to be the next Student Record Return NAMES Docsity.com Non-Procedural (SQL) SELECT Name FROM Student, Enrolment WHERE (Student.ID = Enrolment.ID) AND (Enrolment.Code = (SELECT Code FROM Module WHERE Title = ‘Database Systems’)) Docsity.com Implementing E/R Designs • Given an E/R design • The entities become SQL tables • Attributes of an entity become columns in the corresponding table • Relationships may be represented by foreign keys Enrolment Student Module In Has ID Code Title Name Address Year Assignment Exam Assignment Exam Credits Docsity.com Entities and Attributes • Each entity becomes a table in the database • The name of the table is often the name of the entity • The attributes become columns of the table with the same name • A table called Student • With columns for ID, Name, Address, and Year Student ID Name Address Year Docsity.com CREATE TABLE CREATE TABLE <name> ( <col-def-1>, <col-def-2>, : <col-def-n>, <constraint-1>, : <constraint-k>) • You supply • A name for the table • A list of column definitions • A list of constraints (such as keys) Docsity.com Example CREATE TABLE Student ( stuID INT NOT NULL, stuName VARCHAR(50) NOT NULL, stuAddress VARCHAR(50), stuYear INT DEFAULT 1) Student ID Name Address Year Docsity.com Constraints CONSTRAINT <name> <type> <details> • Common <type>s • PRIMARY KEY • UNIQUE • FOREIGN KEY • INDEX • Each constraint is given a name - Access requires a name, but some others don’t • Constraints which refer to single columns can be included in their definition Docsity.com Primary Keys • Primary Keys are defined through constraints • A PRIMARY KEY constraint also includes a UNIQUE constraint and makes the columns involved NOT NULL • The <details> for a primary key is a list of columns which make up the key CONSTRAINT <name> PRIMARY KEY (col1, col2, …) Docsity.com Relationships • Depends on the type • 1:1 are usually not used, or can be treated as a special case of M:1 • M:1 are represented as a foreign key from the M-side to the 1 • M:M are split into two M:1 relationships Enrolment Student Module In Has ID Code Title Name Address Year Assignment Exam Assignment Exam Credits Docsity.com Representing Relationships • The Enrolment table • Will have columns for the Exam and Assignment attributes • Will have a foreign key to Student for the ‘has’ relationship • Will have a foreign key to Module for the ‘in’ relationship Enrolment Student Module In Has ID Code Title Name Address Year Assignment Exam Assignment Exam Credits Docsity.com Foreign Keys • Foreign Keys are also defined as constraints • You need to give • The columns which make up the FK • The referenced table • The columns which are referenced by the FK CONSTRAINT <name> FOREIGN KEY (col1,col2,…) REFERENCES <table> [(ref1,ref2,…)] • If the FK references the PK of <table> you don’t need to list the columns Docsity.com