본문 바로가기

데이터베이스

DBMS - (18) BCNF(Boyce-Codd Normal Form) DBMS - (18) BCNF(Boyce-Codd Normal Form) Relational design by decomposition - 'mega' relations + properties of the data - system decomposes based on properties. - final set of relations satisfies normal form. - functional dependencies => BCNF - multivalued dependences => 4NF Decomposition of a relational schema. R(A1,A2,...,An) = R(A*) R1(B1,B2,...,Bn) = R(B*) R2(C1,C2,...,Cn) = R(C*) - B* U C* ..
DBMS - (17) functional dependency DBMS - (17) functional dependency Relational desgin by decomposition - 'mega' relations + properties of the data - system decomposes based on properties - final set of relations satisfies normal form (*normal form? no anomaly, no lost information) - functional dependency -> BCNF - multivalued dependency -> 4NF Functional dependency is generally useful concept. - Data storage ( compression ) - Re..
DBMS - (16) relational design overview DBMS - (16) relational design overview Good Schema를 만드는 방법? Designing a database schema - Usually many designs possible. - Some are much better than others. - How do we choose? Often use higher-level design tools, but, ... - Some designer go straight to relations. - Useful to understand why tools produce certain schemas. Very nice theory for relational database design => Normal form. 학번 과목코드 이름 ..
DBMS - (15) join operator DBMS - (15) join operator Cross Product == X Inner Join == ⋈θ Natural Join = ⋈ Inner Join using(attr) == ⋈θ Left | Right | Full outer join Inner Join Q1) select distinct sName, major from Student, Apply where Student.sID = Apply.sID; Q2) select distinct sName, major from Student [inner] join Apply on Student.sID = Apply.sID; Q1과 Q2의 쿼리 결과는 동일하다 Q3) select sName, GPA from Student, Apply where Stu..
DBMS - (14) data modification statement DBMS - (14) data modification statement Inserting new data(2 methods) - insert into Table values(A1,A2,...,An); - insert into Table [select-statement]; Deleting existing data - delete from Table where condition; Updating existing data - update Table set A1=Expr1, A2=Expr2,..., An=Expr(n) where condition; Insert Q1) insert into College values('Carnegie Mellon', 'PA', 11500); Q2) insert into Apply..
DBMS - (13) null value DBMS - (13) null value NULL value? undefined or unknown Example College(cName, state, enrollment) Student(sID, sName, GPA, size of HS) Apply(sID, cName, major, decision) GPA, decision은 null이 허용된다. insert into Student values(432, 'Kevin', null, 1500); insert into Student values(321, 'Lori', null, 2500); Q1) select sID, sName, GPA from Student where GPA > 3.5 or GPA 3.5 or GPA
DBMS - (12) aggregation function DBMS - (12) aggregation function SELECT A1,A2,...,An FROM R1,R2,...,Rm WHERE condition GROUP BY columns HAVING condition aggregation function : max, min, sum, avg, count Q1) select avg(GPA) from Student; 전체 학생들의 평균 GPA Q2) select min(GPA) from Student, Apply where Student.sID = Apply.sID and major = 'cs'; CS에 지원한 학생들의 최소 GPA Q3) select avg(GPA) from Student, Apply where Student.sID = Apply.sID a..
DBMS - (11) sub-query[from] DBMS - (11) sub-query[from] Q1) select sID, sName, GPA, GPA * (sizeHS / 1000.0) as ScaledGPA from Student where GPA * (sizeHS / 1000.0) - GPA > 1.0 or GPA - GPA * (sizeHS / 1000.0) > 1.0 Q2) select sID, sName, GPA, GPA * (sizeHS / 1000.0) as ScaledGPA from Student where abs(GPA * (sizeHS / 1000.0) - GPA) > 1.0 abs(=absolute) keyword로 절댓값을 사용 Q3) select * from (select sID, sName, GPA, GPA * (size..

티스토리툴바