ipl-logo

IEDA 3300 CHEAT SHEET

.pdf
School
The Hong Kong University of Science and Technology**We aren't endorsed by this school
Course
IEDA 330
Subject
Information Systems
Date
Jan 11, 2025
Pages
5
Uploaded by MateMantisPerson998
ENTITY RELATIONSHIP (ER) MODELEntity:table (Employee, Department)Weak entity:X key attributes// Attribute :Properties(Name,Age,Gender)Type 1:Simple(Last/First name, Age)Type 2 :Composite(D.O.B → DD/MM/YY, Address)Type 3:Multivalued(Phone number, Author)Type 4:Derived(D.O.B stored →age can bederived/ Grades → CGI)Key:Unique attribute (ID No.)Degree of relationship (d.o.r):no.of entities involved*entities can have several relationships*relationships can have own attributes /Roles:Relationships between entities of same typeCardinality constraints(d.o.r of binary) : 1:1 / 1:N / N:M / M:1Participation constraints:Total (2) or Partial (1) participationStudents DO NOT have to join society Society MUST have min.onestudent—---------------------------------------------------Relation :Table /Columns: Attributes/ Rows:TuplesSchema: Student ( Name, SID, Age, GPA)Key: minimalSuper KeySuperkey: (1 or more) attributes that can help identifyForeign Key: attribute that can reference to another tableER Diagram → Relational SchemasStep 1: Select Primary key for each regular Entity and MARK itStep 2: (for binary relation) Entity : S and Tfor 1:1add PK(S) as FK(T) // PK(T) as FK(S)//for 1:N(S:N side) add PK(T) as foreign key in S//for M:NCreate new relation (P) , with PK’s of S and T as FK’s of P + attributes of PStep 3: For each weak entity (W), whose identifying entity is (E)Select a primary key FOR (E) andMARK itStep 4: For each MV attribute (A), create new relation (R) including A, plus PK of the entity/relationshipcontaining AStep 5: For each relationship, R, with degree > 2 , Create a relation R, with PK of eachparticipating entity as FK, plus all simple attributes of R[Good design: store all information in the system & disallowsdata anomalies→ Avoid redundancy: Avoid too manyNULL ]CANNOT JOIN NON-PRIMARY KEY even if present in both tablesAttributes X→ Y ( for any two tuples , t1and t2, if t1[X] = t2[X], then t1[Y] = t2[Y]A1. (Reflexive). If Y is subset of X, then X → YA2. (Augmentation). If X → Y, then XZ → YZ(XZ == Xunion Z)A3. (Transitive). If X → Y and Y → Z, then X → ZA4. (Decomposition). If X → YZ, the X → Y andX → Z
Background image
A5. (Union). If X → Y and X → Z, then X → YZA6. (Pseudo Transitive). If X → Y and WY → Z, thenWX → ZFirst normal form (1NF) schema (R) if 1. X any composite attributes, (Name → should be Lname , Fname)2. X any multi-valued attributes, (Numerous courses → should be each course , each row)3. X any nested relations ( No merged columns)Second normal form (2NF) schema (R) if every non-prime attribute A in R is fully functionally dependent onthe primary keyPrime Attribute:An attribute that is a member of the primary keySubset ofROWS/ Tupleof table:Z =SELECT[column/attribute name = condition ] (Table Name)EgSELECT[ DeptNo= 5] (Employee) \\SELECT[(Name != AmyOR( ( SSN =222)AND(DeptNo=5))]Subset ofattributes / COLUMNSof table:Z=PROJECT[column /attributes name ] (Table Name) eg.PROJECT[Name , Ssn] (TABLE NAME)Combine two tables:Z =JOIN[PK = FK ] (Table 1,Table 2 )Topreservetuples that appear in either table only(will fill empty attributes with null / NULL ):Z=LEFT-OUTER-JOIN[condition ] (Table 1,Table 2 ) //RIGHT-OUTER-JOIN[condition ] (Table 1,Table2 )A∪BZ=UNION((SELECT [condition 1](Table 1)),(SELECT [condition 2](Table 1)))A ∩BZ =INTERSECTION((SELECT [condition 1](Table 1)),(SELECT [condition 2](Table 1)))A∪Bc:Z=DIFFERENCE(SELECT [condition 1](Table 1), SELECT [condition 2](Table 1))Selection of specific tuple that appear in all value of another table:Z:DIVIDEBY(Table 1 , Table 2)If the attribute of table 1 and 2 have the same name, [table1name.attribute = table2name.attribute] to joinIf want to join the same table tgt, make it into another table and join them [table1name.attribute =table2name.attribute]Date is also number, can be MAX( ), MIN( ), > / < / =SQL ALLOWS 2 OR MORE
Background image
TUPLES THAT ARE IDENTICAL IN ALL THEIR ATTRIBUTE VALUESSELECTSsn, Lname<attribute name(s) / >FROMEMPLOYEE, DEPARTMENT<table name(s)> eg.WHEREE.Fname=‘John’ANDE.Ssn = D.Ssn AND Salary > 500 ; <condition> eg.GROUPBY<grouping attribute(s)>HAVING<group condition>ORDER BY<attribute name(s) / > DESC/ASCINSERT INTOEmployee (Emp_name, Proj_name,Hours_per_week) ←create table firstSELECT*← all attributesSELECTALLSalary← allows repeatSELECTDISTINCTSalary← appear once onlySELECT ______AS____ { SELECT SUM(salary)/ B.salaryASSalary }SELECTSUM (Salary),COUNT[(*)/(DISTINCTHours)] ,MAX( ),MIN( ),AVG( )null value discardedFROM EmployeeASE, DepartmentASDFROM ( SELECT ____ FROM _____ WHERE _____ )FROM (EmployeeJOINDepartmentONDno=Dnumber )temporarytableFROM (EmployeeLEFT OUTER JOINDepartmentONDno=Dnumber )temporarytableFROM ((EmployeeJOINDepartmentONDno=Dnumber )JOINProjectONProjNo=PNo )FROM (EmployeeNATURAL JOIN(DepartmentAS DEPT(Dno, Dname) )DELETEFROM EmployeeUPDATEEmployeeSETSalary = 1.1*SalaryWHEREDnoIN / NOT IN(SELECTDepartmentNoFROMtablesWHEREPK=FK AND )ANDDnoIN / NOT IN(SELECTDepartmentHeadNoFROMtables )WHEREAddressLIKE‘%Houston%’ \\ AddressLike‘20_ _-_ _ _ _’WHERESalaryBETWEEN3AND4 // ((Salary ≥3) AND (Salary ≤ 4))ANDDno = 5WHEREFNameIS\\ IS NOT NULLWHEREBDate=( SELECTMIN(BDate)FROMEmployee )← = Exactly thatvalueWHEREno conditionsGROUP BYProject_Name, Project_numberHAVINGCOUNT(*) > 2 ← ( SELECT COUNT(*) )ORDER BYE.SsnDESC, SalaryASC
Background image
( SELECT< attribute name(s) >FROM< table name(s)>WHERE< condition > )UNIONA∪B( SELECT< attribute name(s) >FROM< table name(s)>WHERE< condition > )[ WHEREDnoIN (SELECT| IN(SELECT> ALL(SELECTNOT EXISTS//EXISTS(SELECTFROM|FROMFROMFROMWHERE) |WHERE)WHERE )WHERE )|ORAND /EXCEPT|(SELECTNOT EXISTS//EXISTS(SELECT|FROMFROM|WHERE)WHERE)INSERT INTO EmployeeVALUES (‘Richard’, ‘K’, ‘Marini’, ‘653298653’, ‘1962-12-30’, ’98 Oa Forest, Kety, TX’, ‘M’, 37000,‘653298653’, 4)INSERT INTO Employee(Fname, Lname, Ssn, BDate)VALUES (‘Richard’, ‘Marini’, ‘653298653’, ‘1962-12-30’)SELECT DeptNameFROM EmpDeptWHERE DeptId IN(SELECT DepartmentFROM Employee ,EmpDeptWHEREDepartment = DeptIdAND Empid > 103)SELECT EmpNameFROM EmployeeWHERE EmpHeadId IN[=](SELECT Empid
Background image
FROM EmployeeWHERE EmpName = ‘Abhishek’SELECT (*)FROM EmployeeWHERE Department IN(SELECT DeptIdFROM EmpDeptWHERE DeptName = ‘Development’)
Background image