1 / 67

Relational Database Structure

Relational Database Structure. - Relation : set of tuples - Tuple : 1) ordered set (i.e. list) of values from domain D1, D2, ..., Dk 2) mapping from attribute names to values in the domains of the attributes => standard definition

jabari
Télécharger la présentation

Relational Database Structure

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Relational Database Structure - Relation : set of tuples - Tuple : 1) ordered set (i.e. list) of values from domain D1, D2, ..., Dk 2) mapping from attribute names to values in the domains of the attributes => standard definition Ex) Relation CITIES First attribute = Name Second attribute = State Third attribute = Population <Buffalo, NY, 800> <San Jose, CA, 100> <Las Vegas, Nevada, 900> CITIES Name State Popularation Buffalo NY 800 San Jose CA 100 Olando FL 900

  2. Relational Database Structure (cont'd) - Relation scheme : list of attributye names (R) - Relation instance : relation (r) - (Relational) Database scheme : collection of relation schemes - Key (of relation scheme R) : subset K = {A1, A2, ... , Am} of attributes of R such that no two tuples of relation r(R) can have the same value for K - Primary key : a key of the relation scheme that we select to identify tuples Ex) primary key CAR Licence-number Engine_number Maker Model-year Texas-ABC-123 F300001 KIA 1991 Michigan-ZEP-453 X21023 Hyundai 1995 key

  3. Integrity Constraints - Entity integrity constraint no primary key can be null - Referential integrity constraints (foreign key constraints) foreign key : set of attributes which is the primary key of another relation (ex) Dept = (Dname, Manager, Budget) Employee = (Ename, Bdate, Salary, Dname) Every value of the foreign key must exist in the set of the corresponding primary key values of another relation(Dept)

  4. Database Operation (Formal Language) • - Operation to manipulate a relation is needed • - There are two types of operation, Relational Algebra and • Relational Calculus • Relational Algebra • query is expressed by applying specialized algebraic • operators operators => SQL • * procedural : how the desired data is retrieval • Relational Calculus • query is described a desired set of tuples by specifying a • predicate the tuples must satisfy => QUEL, QBE • * nonprocedural : what data is needed

  5. Relational Algebra Relational Algebra is set of operators to manipulate a relation (1) Classification by Set or Relational operaors - Set operators : Union () R S Intersection ( ) R S Set Difference ( ) R S Cartesian product () R S - Relational operator : Selection Projection Join Division

  6. Relational Algebra (cont'd) (2) Classification by Primitive or Composite operators - Primitive operators : Union Intersection Cartesian product Selection Projection - Composite operator : Join Intersection Division basic element operation composed of primitive operators

  7. Relational Algebra (cont'd) (1) Union ( R S ) - set of tuples that are in R or S or both S d1 d2 d3 b1 b2 b3 e1 e2 e3 R a1 a2 a3 b1 b2 b3 c1 c2 c3 R U S a1 a2 a3 b1 b2 b3 c1 c2 c3 d1 d2 d3 e1 e2 e3 only once • <b1, b2, b3> appears only once in R S • (i.e., deplicates are not eliminated. Remember • a relation is a set) • A set allowing duplicates is called a multiset

  8. Relational Algebra (cont'd) • Definition Union-compatible • 1) Relations are of the same arity • 2) attribute in the same position have compatible domains • Union can be formed only for union-compatible relations • (2) Intersection ( R S ) • - set of tuples that are in both R and S. i.e., R - (R - S) • - relations must be union-compatible R S b1 b2 b3 S d1 d2 d3 b1 b2 b3 e1 e2 e3 R a1 a2 a3 b1 b2 b3 c1 c2 c3

  9. Relational Algebra (cont'd) (3) Cartesian Product ( R S ) - R : set of k1-tuples S : set of k2-tuples R S : set of (k1 + k2)-tuples S d1 d2 d3 e1 e2 e3 R a1 a2 a3 b1 b2 b3 c1 c2 c3 R S a1 a2 a3 b1 b2 b3 a1 a2 a3 e1 e2 e3 b1 b2 b3 d1 d2 d3 b1 b2 b3 e1 e2 e3 c1 c2 c3 d1 d2 d3 c1 c2 c3 e1 e2 e3

  10. Relational Algebra (cont'd) (4) Set difference ( R - S ) - set of tuples in R but not in S - must be union-compatible S d1 d2 d3 b1 b2 b3 e1 e2 e3 R a1 a2 a3 b1 b2 b3 c1 c2 c3 R - S a1 a2 a3 c1 c2 c3

  11. Relational Algebra (cont'd) (5) Selection, F(R) - F : selection predicate - select only those tuples that satisfy F Example Age > 30 AND Dept = 512 EMP EMP Name Dept Age Adams 512 25 Jones 512 40 Gilbert 400 32 Result

  12. Relational Algebra (cont'd) (6) Projection, i1, i2, i3, ... , im (R) - take a subset of attributes from R Example Name, Age EMP 1, 3 EMP) EMP Name Dept Age Adams 512 25 Jones 512 40 Gilbert 400 32 Result

  13. Relational Algebra (cont'd) (7) Join, R join predicate S - set of tuples in R X S satisfying the join predicate - join predicate : arithmetic comparison expression involving attribute in R as well as in S EMPEMP.Child = Children.Cname Children (7.1) Equijoin, R R.a = S. b S Children Cname School Mary Foxlane Charlie Bronxville EMP Ename Age Child Jones 45 Mary White 25 Charlie Ename Age Child Jones 45 Mary White 25 Charlie Cname School Mary Foxlane Charlie Bronxville

  14. Relational Algebra (cont'd) (7.2) Thetajoin, RR.a S.b S -  : any arithmetic comparision operator (=, <, >, <=, >=, !=) Example R A B C 1 2 3 4 5 6 7 8 9 S D E 3 1 6 2 RB < D S A B C D E 1 2 3 3 1 1 2 3 6 2 4 5 6 6 2

  15. Relational Algebra (cont'd) (7.3) Natural Join, RS - Equijoin based on attributes of the same name - only one attribute of the same name is projected in the result Example (7.1) Equijoin, R R.a = S. b S Children Childname School Mary Foxlane Charlie Bronxville EMP Ename Age Childname Jones 45 Mary White 25 Charlie EMPChildren = School Foxlane Bronxville Ename Age Childname Jones 45 Mary White 25 Charlie

  16. . . . . . . Relational Algebra (cont'd) (8) Division, RS - Let two attribute sets X = {A1, A2, ... , Am} Y = {B1, B2, ... , Bm} - Two relation R(X, Y), S(Y) have above attribute set - R S => can be divided by S(Y) and only one tuple Example S C D c d e f A B a b e d R A B C D a b c d a b e f b c e f e d c d e d e f a b d e =

  17. Relational Algebra (cont'd) • Semijoin • R S = R (S[X Y]) • Outerjoin R의 애트리뷰트 S의 애트리뷰트 • - R + S • - result relation have all tuples of the two relations • - if two tuples is not common, the result relation is filled null • attribute value • See the Lee, S. H.'s book p.141

  18. Relational Calculus (Introduction) - Query is described a desired set of tuples by specifying a predicate the tuples must satisfy - Another representation of operation for relation - Based on predicate calculus - Declarative not procedural -Tuple Relational Calculus Domain Relational Calculus - Equivalent in expressive power to Relational Algebra = Tuple Relational Calculus = Domain Relational Calcucus

  19. Tuple Relational Calculus • What ? represent tuple calculus expression to specify • user's request • Basic Concepts • A query in tuple relational calculus is expressed as • { t | P(t) } t : tuple variable • P(t) : well-formed formula (WFF) = condition • t in P(t) : bound variable • A well-formed formula produces True or False given • an interpretation • Well-Formed Formula (WFF) • Atomic formulas connected by logical connectives, AND, OR, • NOT and quantified by quantifiers, A (existential quantifier), • E (universal quantifier) result tuples

  20. Tuple Relational Calculus (cont'd) • Atomic Formula • 1. R(s) : R is a relation name • s is tuple variable • means that tuples s is in relation R. • 2. ti[A] tj[B] •  : comparision operator ( =, <, >, <=, >=, != ) • ti , ti : tuple variables • A, B : attributes • e.g., t1.A = t2.B (specifies  join) • 3. ti[A] constant • e.g., ti[A] = 'Mary' (specifies selection)

  21. Tuple Relational Calculus (example) • Tuple Relational Calculus Query • Consider relations EMP(Name, MGR, DEPT, SAL) • CHILDREN(Ename, Cname, Age) • Q1 : Retrieve SALary and CHILDREN's Name of EMPloyees • whose manager is 'White' • {t | ( E e)( E c) (EMP(e) AND CHILDREN(c) AND • /* instantiate tuple variables • e[Name] = c[Ename] AND /* join condition • e[MGR] = 'White' AND /* selection condition • t[1st attribute] = e[SAL] AND • t[2nd attribute] = c[Cname] ) } /* projections result tuples

  22. Domain Relational Calculus (introduction) • What ? represent domain variables expression to specify • user's request • Basic Concepts • A query in domain relational calculus is expressed as • { x1, x2, ... , x2 | P(x1, x2, ... , x2) } • x1, x2, ... , x2 : domain variables • P(t) : well-formed formula (WFF) = condition • Well-Formed Formula (WFF) • Atomic formulas connected by logical connectives, AND, OR, • NOT and quantified by quantifiers, E (existential quantifier), • A (universal quantifier) result tuples

  23. Domain Relational Calculus (cont'd) • Atomic Formula • 1. R(x1, x2, ... , x2 ) : R is k-ary relation R(t) • xi is domain variable or constant • 2. x y (specifies join) ti[A] tj[B] •  : comparision operator ( =, <, >, <=, >=, != ) • x, y : domain variables • 3. x constant (specifies selection) ti[A] constant • e.g., ti[A] = 'Mary' (specifies selection)

  24. Domain Relational Calculus (example) • Domain Relational Calculus Query • Consider relations EMP(Name, MGR, DEPT, SAL) • CHILDREN(Ename, Cname, Age) • Q1 : Retrieve SALary and CHILDREN's Name of EMPloyees • whose manager is ’White' • {q, s | ( E u) (E v) ( E w) ( E x) ( E y) • (EMP(u, v, w, q) AND CHILDREN(x, s, y) AND • /* instantiate domain variables • u = x AND /* join condition • v = 'White' } /* selection condition • /* projections is implied (q, s)

  25. Commercial Database Languages • Background • The formal languages(operations) provide a concise notation • for representing queries. • However commercial database systems require a more • user friendly query language • Commercial Language vs Formal Language • SQL Relational Algebra + Relational Calculus • QBE Domain Relational Calculus (p. 204- 209) • QEUL Tuple Relational Calculus ( X )

  26. SQL (History) • Belief History of SQL Standards CODASYL Database Facility SQL NDL SQL86 (about 100 pages) SQL89(about 115 pages) SQL2 (about 500 pages) SQL3(over 1000 pages) 1960's - 1970's : DBTG in CODASYL The late 1970's : ANSI Since the early 1980's :ISO

  27. SQL (Overview) • Overview of SQL • - Previously called SEQUEL(Structured English QUEry Language) • - SQL has serveral parts : • Data Definition Languae (DDL) : • defining relation schemes, deleting relations, creating indices • modifying relation schemes • Data Manipulation Languae (DML) : • retrieve, insert, delete, modifying tuples in the database • Embedded Data Manipulation Languae • embedded form of SQL is designed for use within • general-purpose programming language such as C, Fortran

  28. SQL (Overview) • View Definition • SQL DDL for defining views • Authorization • Specifying access rights to relations and views • Transaction Control • Specifying the beginning and ending of transactions

  29. DDL (Overview) • - A relation is called a Table • A table allows duplicate tuples • A Multiset rather than a set • Duplicates must be eliminated explicitly • (by using 'SELECT DISTINCT') • - A table consist of three types : • Base table : created by DDL • View : virtual table based on base table (such as, window) • Temporary table : do not created by DDL, but result of • query processing

  30. DDL (cont'd) • Creating Base Table • created an empty table which have no data just now • CREATE TABLE base-table-name • (column-name data-type [NOT NULL] • { , column-name data-type [NOT NULL] }* ) ; • * [ ] : optional part, { }* : repeated part • Example • CREATE TABLE STUDENT • (SNO INTEGER NOT NULL, • SNAME CHAR(20), • YEAR INTEGER, • DEPT CHAR(4), • PRIMARY KEY (SNO)) ;

  31. DDL (cont'd) • Data Type • INTEGER : 4-byte integer • SMALLINT : 2-byte integer • FLOAT(p) : floating point integer • DECIMAL(i, j) : fixed point integer • CHAR(n) : n-charater string • DATE : date (yymmdd) • TIME : time (hhmmss)

  32. DDL (cont'd) • Altering Base Table • modify base table structure (scheme) • ALTER TABLE base-table-name • ADD column-definition column-type • | DROP column-name ; • Example • ALTER TABLE STUDENT • ADD ADDR CHAR(60) ; • * STUDENT table have five columns. But added column is filled • null values.

  33. DDL (Create Index) • Creating Index • creating index file for fast query processing • but, they have an overhead to maintain index • CREATE [UNIQUE] INDEX index-name • ON base-table (column-name [ASC | DESC] • {, column-name [ASC | DESC] }* ) • [CLUSTER] ; • Example • CREATE UNIQUE INDEX CNINDEX • ON COURSE(CNO) ; • * COURSE table have an index. We can access the table COURSE • by CNO values

  34. DDL (Create Index) • Example • CREATE INDEX XINDEX • ON REL(COL1, COL2 DESC, COL3) • CLUSTER ; • * REL table have an index XINDEX which is consist of COL1, • COL2(descending order), COL3. • * Record of REL table is stored in the order of XINDEX values. • Physically, XINDEX values are adjacent by sequence of • XINDEX values.

  35. DDL (Drop Table) • Deleting Table • Deleting base table which can not be accessed forever • DROP TABLE base-table-name ; • Example • DROP TABLE ENROL ; • * This statement delete view and index as well as base table.

  36. DDL (Drop Index) • Deleting Index • Deleting index from base table • DROP INDEX index-name ; • Example • DROP INDEX XINDEX ;

  37. DML • SQL DML have following facilities • - retrieve, insert, delete, modifying tuples in the database • - SELECT, INSET, DELETE, UPDATE statement • - they manipulate to the base table or view • SELECT (basic structure) • - basic SQL structure for retriving • - selection and projection operation • SELECTcolumn-list • FROMtable-list • WHEREcondition ; basic form

  38. DML (Select) • Example • List the names and student-numbers of students who major in • 'Computer Engineering' in STUDENT table. • SELECT SNAME • FROM STUDENT • WHERE DEPT = 'Computer Engineering' • SNAME SNO • Kim Jin Oh 901234 • Park Eun Yeong 920049 result is a table (temporary table)

  39. DML (Select) • SELECT (Eliminating Duplicates) • - delete duplicate tuples in result table • Example • List DEPT in STUDENT table. • SELECT DISTINCT DEPT • FROM STUDENT ;

  40. DML (Select) • SELECT (All the columns) • - wildcard * usage • Example • List STUDENT table. • SELECT * • FROM STUDENT ; • is equivalent to SELECT SNO, SNAME, YEAR, DEPT • FROM STUDENT ;

  41. DML (Select) • SELECT (Ordeing the result tuples) • Example • List all student whose scores are greater than 90 descending • order by SNO, ascending order by CNO. • SELECT SNO, CNO • FROM ENROL • WHERE MIDTERM >= 90 • ORDER BY SNO DESC, CNO ASC ; • 400 C312 • 400 C324 • 300 C312 • 300 C423 • 100 C413 • 100 E412 first ordering second ordering in first ordering

  42. DML (Select) • SELECT (With arithmetic expression and charater string) • Example • For each student who enrol the 'Database', • list SNO and MIDTERM + 3 with 'Midterm =' string. • SELECT SNO, 'Midterm =', MIDTERM+3 • FROM ENROL • WHERE CNO = 'Database' ; • SNO • 300 Midterm = 93 • 400 Midterm = 83 • 500 Midterm = 73

  43. DML (Select) • SELECT (Join, Selection from multiple tables) • Example • For student who enrol the 'Database', list SNAME, DEPT, • and GRADE. • SELECT STUDENT.SNAME, STUIDENT.DEPT • ENROL.GRADE • FROM STUDENT, ENROL • WHERE STUDENT.SNO = ENROL.SNO AND • ENROL.CNO = 'Database' ; • SNAME DEPT GRADE • Kim CE A • Chung CE A • Lee Math B

  44. DML (Select) • SELECT (Self Join) • Example • For each employee whose salary exceeds his/her manager's • salary, list the employee's name and his/her manager's name. • SELECT X.Name, Y.Name • FROM EMP X, EMP Y • WHERE X.Sal > Y.Sal ambiguous; thus, use table labels X and Y

  45. DML (Select) • SELECT (Aggregate function) • - Operate on aggregates of tuples • - The result of an aggregate function is a single value • - To applying single column • Functions • AVG (average) - only for numeric type • SUM (total) - only for numeric type • COUNT(count) • MAX (maxmun) • MIN (minimum)

  46. DML (Select) • Example • Count number of students in STUDENT table. • SELECT COUNT(*) • FROMSTUDENT ; • Result • 5 * Result include duplicated tuples COUNT(DISTINCT CNO) don't have duplicated tuples

  47. DML (Select) • Example • How many courses are enrolled by student (SNO = 300)? • SELECT COUNT(DISTINCT CNO) • FROMENROL • WHERESNO = 300 ; • Result • 2 * COUNT(DISTINCT CNO) don't have duplicated tuples

  48. DML (Select) • Example • Find average of midterm examination of 'Database'. • SELECT AVG(Midterm) • FROMENROL • WHERECno = 'Database' ; • Result • 87

  49. DML (Select) • SELECT (Group by) • - Grouping by specified column name • Example • Find average of each course's final examination. • SELECT Cno, AVG(Final) • FROMENROL • GROUP BY Cno ; • Result • Cno • Database 91 • Architecture 88 • Data Structure 87

  50. DML (Select) • SELECT (Having) • - Having clause specify retieval condition about each group • Example • Find average of each course which is enrolled more than • 10 students • SELECT Cno, AVG(Final) • FROMENROL • GROUP BY Cno • HAVING COUNT(*) >= 10 ; • Result • Cno • Database 91 • Data Structure 87 We think one group if Group by/Having clause is omitted

More Related