ITM 500 Notes for ENTIRE COURSE

39 Pages
990 Views

Department
Information Technology Management
Course Code
ITM 500
Professor
Raymond Moss

This preview shows pages 1,2,3,4. Sign up to view the full 39 pages of the document.
Description
ITM 500 NOTES Faculty Registration Entities: Assignment students, faculty, courses, offerings, enrollments Relationships: faculty teach offerings, Grade students enroll in Course offerings, offerings made Scheduling Recording of courses, ... University Database Lec 1: Overview • Database Characteristics o Consistent (rogers postal code duplication example, doesn’t mean accuracy) o Inter-related (maximize relationship b/w data) o Shared o Independent (SQL can use all types of programming programs) o Non redundant • University Database o • Database Management System (DBMS) o Collection of components that support data acquisition, dissemination, storage, maintenance, retrieval, and formatting o Enterprise DBMSs  supports mission critical information systems; very large dbs, many users, tight performance requirements o Desktop DBMSs  end user departments and small databases o Major part of information technology infrastructure • Database Definition o Analysis Business Information Needs o Construct Logical Data Model o Implement Physical Tables and Relationships • University Database (ERD Model) Faculty Offering FacSSN OfferNo Course FacName CourseNo (FK) CourseNo FacDept OffTerm FacRank CrsDesc FacSalary OffYear FacSSN (FK) FacHireDate Enrollment Student OfferNo (FK) StdSSN (FK) StdSSN EnrGPA StdName StdMajor StdGPA o o Relational Model Enrollment Student OfferNo StdSSN StdSSN StdName EnrGPA StdMajor StdGPA Faculty FacSSN FacName Offering FacDept OfferNo FacRank CourseNo FacSalary OffTerm FacHireDate OffYear FacSSN Course CourseNo CrsDesc o • Nonprocedural Access o Query: request for data to answer a question o Indicate what parts of database to retrieve not the procedural details o Improve productivity and improve accessibility o SQL SELECT statement and graphical tools • Procedural Language Interface o Combine procedural language with nonprocedural access o Why o Batch processing (collect loan applications and process together) o Customization and automation o Performance improvement • Logical Database Design o Information content of the database o Entity relationship diagram (ERD) showing entity types and relationships o Functionality all important o Analyze design for excessive redundancies o Normalization: tool to reason about redundancies • Physical Database Design o Convert ERD to table design o Physical characteristics of the data o Minimize response time without consuming excessive resources o Tradeoffs: retrieval versus update o Flexible designs versus specialized designs o Indexes, data placement o Add constraints to enforce business rules • Tables o Relational database is a collection of tables o Heading: table name and column names o Body: rows, occurrences of data o • Relationships o • Integrity Rules  Entity integrity: primary keys  Each table has column(s) with unique values  Ensures entries are traceable o Referential integrity: foreign keys  Values of a column in one table match values in a source table  Ensures valid references among tables • Definitions o Primary key: a designated candidate key; cannot contain null values o Foreign key: column(s) whose values must match the values in a candidate key of another table o Null value: special value meaning value unknown or inapplicable o Entity integrity  No two rows with the same primary key value  No null values in any part of a primary key o Referential integrity  Foreign keys must match candidate key of source table  Foreign keys can be null in some cases • Referenced Rows  Referenced row  Foreign keys reference rows in the associated primary key table  Enrollment rows refer to Student and Offering o Actions on referenced rows  Delete a referenced row  Change the primary key of a referenced row  Referential integrity should not be violated • Possible Actions o Restrict: do not permit action on the referenced row o Cascade: perform action on related rows o Nullify: only valid if foreign keys accept null values o Default: set foreign keys to a default value Lec 2: --Example 1. List all details for all students --(i.e. Write a query that will display the data in --all the columns for all the rows in the Student table.) SELECT * FROM Student; --Example 2. List the SSN, Name and Major for all students--(i.e. Write a query that will display the data in --StdSSN, StdName and StdMajor columns for all the rows in --the Student table.) SELECT StdSSN, StdName ,StdMajor FROM Student; --Example 3. List all the details for student 234567890--(i.e. Write a query that will display the data in --all the columns for the student with the SSN 234567890.) SELECT * FROM Student WHERE StdSSN = '234567890' --Example 4. List all the details for student James--(i.e. Write a query that will display the data in --all the columns for the student with the name James.) SELECT * FROM Student WHERE StdName = 'James' --Example 5. List the SSN and Name for all students in the --Finance major SELECT StdSSN, StdName FROM Student WHERE StdMajor = 'FIN' --Example 5 Debugging SELECT StdSSN, StdName, StdMajor FROM Student WHERE StdMajor = 'FIN' --Example 6. List the SSN, Name and Major for all students --with a GPA over 3 SELECT StdSSN, StdName, StdMajor FROM Student WHERE StdGPA > 3 --Example 6a Debugging SELECT StdSSN, StdName, StdMajor, StdGPA FROM Student WHERE StdGPA > 3 --Example 7. List the SSN, Name, GPA and Major for all-- students with a GPA of at least 3 SELECT StdSSN, StdName, StdMajor, StdGPA FROM Student WHERE StdGPA >= 3 --Example 8. List all details for the students not in --the Finance major SELECT * FROM Student WHERE StdMajor != 'FIN' --Example 9. List all details for the students in the --ITM major with a GPA less than 4. SELECT * FROM Student WHERE StdMajor = 'ITM' AND StdGPA < 4 --Example 10. List all details for the students that are-- in either the ITM major or have a GPA less than 4. SELECT * FROM Student WHERE StdMajor = 'ITM' OR StdGPA < 4 --Example 11. List all details for the students that are-- neither in the ITM major nor have a GPA less than 4. SELECT * FROM Student WHERE NOT (StdMajor = 'ITM' OR StdGPA < 4) --or SELECT * FROM Student WHERE StdMajor <> 'ITM' AND StdGPA >= 4 --Example 12. List all details for the students --who are in either the ITM or FIN major (mutally exclusive) SELECT * FROM Student WHERE StdMajor = 'ITM' OR StdMajor = 'FIN' -- or SELECT * FROM Student WHERE StdMajor IN ('ITM','FIN') --Example 13. List all details for the students --who are In either the ITM or FIN major and --who also have a GPA over 3 SELECT * FROM Student WHERE (StdMajor = 'ITM' OR StdMajor = 'FIN') AND StdGPA > 3 --Example 13. Incorrect version (without precedence brackets) SELECT * FROM Student WHERE StdMajor = 'ITM' OR StdMajor = 'FIN' AND StdGPA > 3 --Example 14. List all details for the students --who are in neither the ITM nor the FIN major SELECT * FROM Student WHERE StdMajor <> 'ITM' AND StdMajor <> 'FIN' --or SELECT * FROM Student WHERE NOT (StdMajor = 'ITM' OR StdMajor = 'FIN') --or SELECT * FROM Student WHERE StdMajor NOT IN ('ITM', 'FIN') --Example 15. List all details for the students --with a GPA between 3 and 4 (inclusive) SELECT * FROM Student WHERE StdGPA >= 3.0 AND StdGPA <= 4.0 --or SELECT * FROM Student WHERE StdGPA BETWEEN 3.0 AND 4.0 --Example 15. Restated in more "relaxed" English--List all details for the students --with a GPA of at least 3 but no more than 4. --Example 16. List all details for the students --with a GPA less than 4 but at least 3 (exclusive) SELECT * FROM Student WHERE StdGPA >= 3.0 AND StdGPA < 4.0 --Example 17. List all details for the students in the--Finance major in alphabetical order by name SELECT * FROM Student WHERE StdMajor = 'FIN' ORDER BY StdName ASC --Example 18. List all details for all students--in alphabetical order, name within major SELECT * FROM Student ORDER BY StdMajor, StdName --Example 19. List all details for all students. --Sequence the output from highest to lowest GPA. SELECT * FROM Student ORDER BY StdGPA DESC Lec 3: --Example 1. List the details for all courses--that have the characters "Business" anywhere-- in the course description SELECT * FROM Course WHERE CrsDesc LIKE '%Business%' --Example 1a. List the details for all courses--that's description starts with the characters "Business" SELECT * FROM Course WHERE CrsDesc LIKE 'Business%' --Example 2. List the details for the ITM courses SELECT * FROM Course WHERE CourseNo LIKE 'ITM%' --Example 2a. List the details for all 3rd semester courses SELECT * FROM Course WHERE CourseNo LIKE '___3%' --Example 3. List the details for Faculty hired before Feb 1st 1998 SELECT * FROM Faculty WHERE FacHireDate < '1998/02/01' --other date literal formats SELECT * FROM Faculty WHERE FacHireDate < 'feb 1 1998' SELECT * FROM Faculty WHERE FacHireDate < '02-10-98' SELECT * FROM Faculty WHERE FacHireDate < '02/10/98' --Example 4. List the details for Faculty hired in April 1995 SELECT * FROM Faculty WHERE FacHireDate >= '1995/04/01' AND FacHireDate <= '1995/04/30' --or SELECT * FROM Faculty WHERE FacHireDate BETWEEN '1995/04/01' AND '1995/04/30' --or SELECT * FROM Faculty WHERE YEAR(FacHireDate) = 1995 AND MONTH(FacHireDate) = 4 --Example 4a List Faculty hired in 1995 SELECT * FROM Faculty WHERE FacHireDate >= '1995/01/01' AND FacHireDate <= '1995/12/31' --or SELECT * FROM Faculty WHERE YEAR(FacHireDate) = 1995 --Example 5. List the details for Faculty whose salary if--increased by 10% would exceed $70,000 SELECT * FROM Faculty WHERE FacSalary * 1.1 > 70000 --Example 5a as above but also displayed--is the new calculated salary (with a column alias) SELECT * , FacSalary * 1.1 AS "New Salary" FROM Faculty WHERE FacSalary * 1.1 > 70000 --Example 5b as above but with the output sequenced--by the computed salary. SELECT * , FacSalary * 1.1 AS "New Salary" FROM Faculty WHERE FacSalary * 1.1 > 70000 ORDER BY FacSalary * 1.1 --or SELECT * , FacSalary * 1.1 AS "New Salary" FROM Faculty WHERE FacSalary * 1.1 > 70000 ORDER BY "New Salary" --Example 6. COUNT the total number of students --(i.e. row entries in the Student table) SELECT COUNT(*) AS "Number of Students" FROM Student --Example 7. COUNT the number of students with a GPA under 3.0 SELECT COUNT(*) AS "Number of Students" FROM Student WHERE StdGPA < 3 --Example 8. What is the total value of faculty salaries SELECT SUM(FacSalary) "Total Value of Salaries" FROM Faculty --Example 9 What is the average GPA for students in the ITM major SELECT AVG(StdGPA) AS "Average GPA" FROM Student WHERE StdMajor = 'ITM' --Example 9a What is the highest GPA for students in the ITM major SELECT MAX(StdGPA) AS "Highest GPA" FROM Student WHERE StdMajor = 'ITM' --Example 9b What is the lowest GPA for students in the ITM major SELECT MIN(StdGPA) AS "Lowest GPA" FROM Student WHERE StdMajor = 'ITM' --Example 9c All of 9 above together SELECT AVG(StdGPA) AS "Average GPA", MAX(StdGPA) AS "Highest GPA", MIN(StdGPA) AS "Lowest GPA" FROM Student WHERE StdMajor = 'ITM' --Example 9d List the highest GPA for a student together with their name. --This generates an error. SELECT MAX(StdGPA) AS "Highest GPA" ,StdName FROM Student --Example 10 Count the number of students enrolled (enrollments) in courses SELECT COUNT(*) AS "Number of Enrolled Students" FROM Enrollment --Example 10a Count the number of different Students enrolled in courses SELECT COUNT(DISTINCT StdSSN) AS "Number of different Students" FROM Enrollment --Example 11 Count the number of Offerings SELECT COUNT(*) AS "Number of assigned Offerings" FROM Offering --Example 11a with a Faculty assigned to teach SELECT COUNT(FacSSN) AS "Number of assigned Offerings" FROM Offering --or SELECT COUNT(*) AS "Number of assigned Offerings" FROM Offering WHERE FacSSN IS NOT NULL --Example 11b with no Faculty assigned to teach SELECT COUNT(*) AS "Number of unassigned Offerings" FROM Offering WHERE FacSSN IS NULL Lec 4: --Example 1. List the average gpa for students by major --(i.e. for each major) SELECT AVG(StdGPA) AS "Average GPA" , StdMajor FROM Student GROUP BY StdMajor -- still include the 'GROUP BY' field in the SELECT list even though not -- explicitly requested. Always do this unless otherwise instructed --Example 1a. Sequence the aggregates by highest to lowest average SELECT AVG(StdGPA) AS "Average GPA" , StdMajor FROM Student GROUP BY StdMajor ORDER BY AVG(StdGPA) DESC --Example 1b List the highest student GPA in each major SELECT MAX(StdGPA) AS "Highest GPA" , StdMajor FROM Student GROUP BY StdMajor --the only fields allowed in the SELECT list are used by the function e.g.AVG(FacSalary) -- or are in the GROUP BY clause e.g. FacSalary --Example 2. List the average gpa for each major --but only display averages above 3.0 SELECT AVG(StdGPA) AS "Average GPA" , StdMajor FROM Student GROUP BY StdMajor HAVING AVG(StdGPA) > 3.0 --Example 2a. as per Example 2 above but only for students --whose SSN starts with "1" or ends with "4". SELECT AVG(StdGPA) AS "Average GPA" , StdMajor FROM Student WHERE StdSSN LIKE '1%' OR StdSSN LIKE '%4' GROUP BY StdMajor HAVING AVG(StdGPA) > 3.0 --Example 2b. as per Example 2a above but sequence-- by GPA average from highest to lowest SELECT AVG(StdGPA) AS "Average GPA" , StdMajor FROM Student WHERE StdSSN LIKE '1%' OR StdSSN LIKE '%4' GROUP BY StdMajor HAVING AVG(StdGPA) > 3.0 ORDER BY AVG(StdGPA) DESC --Example 2c. as per example 2 above but this is-- an exception to the implied inclusion in the selected-- list of the GROUP BY column SELECT StdMajor FROM Student GROUP BY StdMajor HAVING AVG(StdGPA) > 3.0 --Example 3 Count the number of enrollments by Student SSN-- but only show the results where the student has enrolled -- in more than 2 offerings SELECT COUNT(*) "No of Enrollments" , StdSSN FROM Enrollment GROUP BY StdSSN HAVING COUNT(*) > 2 --Inner Joins --Example 4. List the details for all students matched --with the details of their enrollments SELECT * FROM Student INNER JOIN Enrollment ON Student.StdSSN = Enrollment.StdSSn --Example 4a In SSN order SELECT * FROM Student INNER JOIN Enrollment ON Student.StdSSN = Enrollment.StdSSn ORDER BY Student.StdSSN -- Example 4b For the Student named "Wells" SELECT * FROM Student INNER JOIN Enrollment ON Student.StdSSN = Enrollment.StdSSn WHERE StdName = 'Wells' -- Example 4c. List only the enrollments details-- for the Student named "Wells" (not his student details) SELECT Enrollment.* FROM Student INNER JOIN Enrollment ON Student.StdSSN = Enrollment.StdSSn WHERE StdName = 'Wells' --Example 5 List the details for students --enrolled in "ITM" courses in SSN sequence SELECT Student.* FROM Student INNER JOIN Enrollment ON Student.StdSSN = Enrollment.StdSSn INNER JOIN Offering ON Enrollment.OfferNo = Offering.OfferNo WHERE CourseNo LIKE 'ITM%' ORDER BY Student.StdSSN --Example 5a Suppress duplicates SELECT DISTINCT Student.* FROM Student INNER JOIN Enrollment ON Student.StdSSN = Enrollment.StdSSn INNER JOIN Offering ON Enrollment.OfferNo = Offering.OfferNo WHERE CourseNo LIKE 'ITM%' ORDER BY Student.StdSSN --Example 5b Using Table aliases for economy of keying SELECT DISTINCT S.* FROM Student S INNER JOIN Enrollment E ON S.StdSSN = E.StdSSn INNER JOIN Offering O ON E.OfferNo = O.OfferNo WHERE CourseNo LIKE 'ITM%' ORDER BY S.StdSSN --Example 6. Insert a row into the Student table, --supplying values for all the columns. INSERT INTO Student (StdSSN, StdName, StdMajor, StdGPA) VALUES ('999999999','ANYONE','ACCT', 0.0) --Example 7. Change the major of Student No '99999999' --from 'ACCT' to 'IS' and give them a GPA of 2.0 UPDATE Student SET StdMajor = 'IS', StdGPA = 2.0 WHERE StdSSN = '999999999' --Example 7a. Change the major of Student No '99999999' --from 'IS' to 'FIN' and increase their GPA by 0.5 points UPDATE Student SET StdMajor = 'FIN', StdGPA = StdGPA + 0.5 WHERE StdSSN = '999999999' --Example 8. Delete Student '999999999' DELETE FROM Student WHERE StdSSN = '999999999' ITM 500 Erwin Notes • • 0,1, or more • • 1 or more • • 0 or 1 • • • 1 and ONLY 1 • • 0,1, or more • AND ONE OFFERING MUST HAVE 0, OR 1 FACULTY • • • No Offer No, move all attributes up • • • • Not every cow has a farmer (farmer side has a O) • • Solid line, farmer id is moved up • • Same as first situation • • Every farmer must have one or more cows (p) • • Cow has (O) and (Z) • Cow will ALWAYS have a farmer, but farmer can only have NONE OR ONE COW Week 7 • Many to many relationship: student can have many offerings, offering can have many students o But FK cant have multiple values rd • Needs 3 party entity to satisfy relationship o Where to store the array of goods o >actions >transformations >resolve many to many • Unique number can be used as PK • Rounded box > if you take out the two FK, box is no longer rounded (unidentifying relationship) • Physician + patient > has time based variables (appointments) o consutationDate, consultationTime • example: author writes book, book has more than one author • example 1 • • • Example 2 • • • Example 3 • • Example 4 • • alternatve • Week 8 : Recursive Relationships • • Instances within same entity • One to many relationship • Always dotted line!!! • “no way of knowing your father before you’re born” • Right click > properties > role name o Rename the new FK that’s currently not showing o Migrated attribute > role name • Always 0 b/c there needs to be ppl at the bottom of the chain who don’t have “ children” • Supervision hierarchy o • Prerequisites o • • • • • • Week 9:
More Less
Unlock Document

Only pages 1,2,3,4 are available for preview. Some parts have been intentionally blurred.

Unlock Document
You're Reading a Preview

Unlock to view full version

Unlock Document

Log In


OR

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


OR

By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.


Submit