[Database] 전체 기본 개념 정리

이 글은 학교 수업(이민수 교수님의 'Database')를 듣고 복습차 정리한 글입니다.

1. 기본 단어

• Schema: logical structure of the Database
• Instance: the actual content of the databases
• Language
  • Data Manipulation Language: Language for accessing and manipulating the data (Known as Query language(includes SQL))
  • Data Definition Language: Language for defining the database schema (DDL compiler generates a set of table templates stored in a data dictionary)
• Data Dictionary: contains a metadata(: data about data)
  • Data Schema, Integrity constraints, Authorization information etc.
• Transaction: a collection of operations that performs a single logical function in a database application. (Properties of Transaction: Atomic, Consistency, Isolation, Durability)
• View: provides mechanism to hide certain data from the view of certain users. known as virtual relation

2. Model of Database
   Models are based on Normalization Theory.
   (Normalization theory: Formalize what designs are bad, and test for them)

• Relational Model: 일반적인 표 구조
  • Domain: the set of allowed values for each attribute (default: null)
  • Attribute values are required to be atomic
  • Relations are unordered, order of tuples is irrelevant

• Entity Relationship Model: models an enterprise as a collection of entities and relationship
  • Entity: a thing or object in the enterprise that exists and is distinguishable from other objects
  • Relationship: an association among several entities using Cardinality(relationship set: a mathematical relation among entites)
    • Cardinality: one to one, one to many, many to one, many to many
    • Weak entity, strong entity
    • Composite and Binary/Multivalued Attributes
    • Specialization and Generalization ( Disjoint, tota/ Overlappingl)
    • Aggregation

• Basic Concepts
  • Keys
    • Superkey: key that identifies a unique tuple of each possible relation
    • Candidate key: minimal superkey
    • Primary key: one of the candidate keys
    • Foreign key: value in one relation must appear in another (related to foreign constraints)

3. Database System Internals

3-1. Transaction Management

• Transaction-Management Component: ensures that the database remains in a consistent(correct) state despite system failures.
• Concurrency-control Manager: controls the interaction among the concurrent transactions, to ensure the consistency of the database.

3-2. Query Processing

• 1. Parsing and Transaction (query > parser and translator > relational-algebra expression)
• 2. Optimization (>optimizer > execution plan)
• 3. Evaluation (>Evaluation Engine > Query output)

3-3. Storage Management
: a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system. > deals with Storage access, File Organization, Indexing and Hashing.

---

4. SQL

• Domain Types in SQL
  char(n), varchar(n), int, smallint, numeric(p,d), real precision, double precision, float(n)

• create table

  create table student(
    ID varchar(5),
    name varchar(20) not null,
    primary key(ID),
    foreign key(name) references department
  );

• delete, add table

  drop table student; # delete table and its content
  delete from student; # delete content but retains table
  alter table r add A D; #add name A with domain D to relation r
  alter table r drop A; #drop attribute A

• select

  select name, course_id
  from instructor, teaches
  where instructor natural join teaches;

use Natural join, between, group by-having, in-not in, some-all, exists- not exists, set operations(union, intersect, except), null/ unknown values, Aggregate function(avg, count)

• insertion

  insert into student
  select name, course_id
  from instructor, teaches
  where instructor natural join teaches;

• update

  update instructor
  set salary = case
                when salary <= 1000000 when salary *1.05
                else salary *1.03
            end

• Natural join

  course inner join prereq on couse.course_id = prereq.course_id
  course natural join left outer join prereq on couse.course_id = prereq.course_id
  course natural join right outer join prereq on couse.course_id = prereq.course_id
  course natural full outer join prereq on couse.course_id = prereq.course_id

• create view
  view has runtime overhead due to it stores definition not data

  create view faculty as
    select ID, name, dept_name
    from instructor
    where dept_name = 'Biology'

• Authorization, Roles

  create role instructor;
  grant instructor to Amit;
  grant select on takes to instructor;
  revoke select on takes from instructor;

5. JDBC
   JDBC is a java api for communicating with database systems supporting SQL.

• Model for communicating with the databse:
  1. Open a connection
  2. Create a statement object
  3. Execute queries using the statement object to send queries and fetch results
  4. Exception mechanism to handle errors