1 / 25

Design process

Design process. Identify entities and attributes What do you want to know? Note that the question of whether something is an attribute or an entity may not be an easy one… Identify relationships Do they have their own attributes?

lundy
Télécharger la présentation

Design process

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. Design process • Identify entities and attributes • What do you want to know? • Note that the question of whether something is an attribute or an entity may not be an easy one… • Identify relationships • Do they have their own attributes? • Think of specific data cases and walk through the business process with this in mind • Does your design still stand up? • Squish things around! • Don’t become attached to one way of thinking about things • Iterate • Until you’ve talked to everyone needed and had time to reflect on the design • More is better!

  2. Normalization • Functional dependencies • Normal forms

  3. Relational theory and SQL • Set theory • projection = select name, age, dept from student • selection = "retrieve students who are not studying LS and are not in their second year" • cartesian product = result of concatenating every tuple in r1 with every tuple of r2.

  4. Projection Selection

  5. Student ID Name • Smith • Wilson Enrolled Student Course 1 1 1 2 2 1 Course • ID Name Descrip • 258 Database Systems • 101 Glass Fusing Cartesian product SELECT * FROM student, course, enrolled WHERE enrolled.student = student.ID;

  6. Joins • Inner • Union • Union compatible sets: respective column datatypes are the same, column order is the same • necessary because datatypes of result set are determined by datatypes of source • Intersect • Minus or Difference • Outer

  7. SQL Review • DDL – data definition language • Create, alter, drop • DML – data manipulation language • Select, update, insert, delete • Other useful commands • Describe, grant, revoke

  8. Create Table CREATE table tablename ( colname datatype constraints, table-constraints); • common datatypes: CHAR(n), VARCHAR(n), int, float (n,n), date, time • common column constraints: primary key, not null, default value • common table-constraints: primary key, foreign key

  9. Create Table Example (1) CREATE TABLE teacher ( PID CHAR(9) PRIMARY KEY, name VARCHAR(30) NOT NULL, salary FLOAT(8,2), bdate DATE, dept VARCHAR(10), FOREIGN KEY (dept) REFERENCES department (DNAME) ON DELETE CASCADE);

  10. Create Table Example(2) CREATE TABLE teaches ( teacherID CHAR(9), coursenum CHAR(3), PRIMARY KEY (teacherID, coursenum), FOREIGN KEY (teacherID) references teacher(PID), FOREIGN KEY (coursenum) REFERENCES course (coursenum));

  11. Create Index CREATE INDEX indexname ON table (col-1, col-2,..col-n); CREATE INDEX idx_teachername ON teacher (name);

  12. Create View CREATE VIEW viewname AS SELECT col-1, col-2,…col-n FROM table-1, table-2,…table-n WHERE conditions;

  13. Create View Example CREATE VIEW vw_teach_course AS SELECT PID, name, dept, coursenum, coursename FROM teacher, teaches, course WHERE teacher.PID = teaches.teacherID AND course.coursenum = teaches.coursenum;

  14. Drop • DROP TABLE name; • DROP TABLE name RESTRICT; • DROP TABLE name CASCADE; • DROP INDEX name; • DROP VIEW name;

  15. Alter Table • ALTER TABLE tablename ADD column datatype; • ALTER TABLE tablename DROP column; • and various other alterations, HIGHLY implementation specific as to availability • changing datatype is risky if data is already in DB

  16. Select SELECT col-1, col-2,...col-n FROM table-1, table-2...table-3 WHERE conditions; • can also do aggregate functions, grouping, and ordering.

  17. Select Example (1) SELECT PID, name, salary FROM teacher WHERE bdate < ‘1952-01-01’ ORDER BY salary;

  18. Select Example (2a) SELECT teacher.name, course.cnum, course.cname FROM teacher, teaches, course WHERE teacher.PID = teaches.teacherID AND course.cnum = teaches.coursenum AND course.cname = ‘Glass Fusing 101’;

  19. Select Example (2b) SELECT teacher.name, course.cnum, course.cname FROM teacher join (teaches join course on teaches.coursenum = course.cnum) on teaches.teacherID = teacher.PID WHERE course.cname = ‘Glass Fusing 101’;

  20. Select Example (3) SELECT teacher.name FROM teacher WHERE teacher.PID in (SELECT teachID FROM teaches WHERE teaches.coursenum = ‘258’);

  21. Select Example (4) SELECT dept, avg(salary) FROM teacher WHERE bdate < ‘1950-01-01’ GROUP BY dept ORDER BY dept;

  22. Group by • A query with a group by clause is executed as follows: • Select all rows that satisfy conditions in the where clause • Form groups according to group by clause • Discard groups that don’t satisfy the having clause • Apply aggregate functions to each group • Retrieve values for aggregates and columns specified in the select clause

  23. Insert INSERT INTO table VALUES (val-1, val-2,...val-3); INSERT INTO table (col-1, col-2,...col-n) VALUES (val-1, val-2,...val-3);

  24. Insert Examples INSERT INTO teacher VALUES (‘123456789’, ‘Mary Brown’, 91253.83, ‘1949-03-21’, ‘English’); INSERT INTO teacher (PID, name, dept) VALUES (‘123456789’, ‘Mary Brown’, ‘English’);

More Related