シラバス

授業ページ

第一回

もっとも簡単なデータベースとして、pythonを使って CSVファイルでデータを保存する方法が紹介されていた。しかし、もちろんこれではだめである。

問題点

データ整合の問題（data integrity)
実装の問題(implementation)
耐久性の問題(durability)

-> これらの問題を解決するためにDBMSを使う

その他、データベースの歴史などつまらない話。edgar coddさんの名前は覚えておいた方がいいかも。IBMの研究者で1970年にRDBの理論を書いた論文を発表した。これは覚えておく価値がある。

結論としては、データを使う側としては、データがどうやって管理されるかは興味がない。きれいなインターフェースだけ提供してほしいと。なのでデータベースを作ろうという話。

第二回

relationlan languagesには、

DML DDL DCL があるってはなし。全体的に、SQLの文法を学んだって感じ。結構難しいものまで扱っているんですよね。

aggregates

avg(col)
min(col)
max(col)
count(col)

String operation

Pattern Matcing

% : matchies any substrings
_ : matchies any one character

String functions

SUBSTRING(S,B,E)
UPPER(S) とかね。

Concatination

||でつなげることができるらしいです。

Date and Time

DATE,TIME型がああるよって話ですね。

Output Redirection

クエリが返す結果を別のテーブルに保存することができる。

SELECT cid INTO CoutseIds FROM enrolled;

て感じで

Output Control

結果を整形することができるって話。昇順に並べたり、降順に並べたり、どのカラムで並べるかを決定したり、何個出力させるかを決定したり、って感じや。

SELECT sid,grade FROM enrolled WHERE cid = '15-721'
ORDER BY grade LIMIT 10;

的な感じですね。

Nested Queries

SELECT name 
FROM student AS s, (SELET sid FROM enrolled) AS e
WHERE s.sid = e.sid;

バックエンドロジックからクエリを発行してデータベースに問い合わせるとき、難しい結果が欲しい場合は、クエリを何個かにわけて実行することがあるけど、これは良くなくて、できるだけ一つにまとめて実行するのがいいらしい。一回にまとめてやったほうがｇ絶対にいい結果が得られるとandyはいっている。

ASSIGNMENT

データベースが与えられるので、目的のタプルを見つけるクエリを答えるっていう問題。

まずはテーブルの構造から行きますかー

sqlite> .tables
akas      crew      episodes  people    ratings   titles

sqlite> .schema akas
CREATE TABLE akas (
  title_id VARCHAR, -- REFERENCES titles (title_id),
  title VARCHAR,
  region VARCHAR,
  language VARCHAR,
  types VARCHAR,
  attributes VARCHAR,
  is_original_title INTEGER
);


sqlite> .schema crew
CREATE TABLE crew (
  title_id VARCHAR, -- REFERENCES titles (title_id),
  person_id VARCHAR, -- REFERENCES people (person_id),
  category VARCHAR,
  job VARCHAR,
  characters VARCHAR
);

sqlite> .schema episodes
CREATE TABLE episodes (
  episode_title_id VARCHAR, -- REFERENCES titles (title_id),
  show_title_id VARCHAR, -- REFERENCES titles (title_id),
  season_number INTEGER,
  episode_number INTEGER
);

sqlite> .schema people
CREATE TABLE people (
  person_id VARCHAR PRIMARY KEY,
  name VARCHAR,
  born INTEGER,
  died INTEGER
);

sqlite> .schema ratings
CREATE TABLE ratings (
  title_id VARCHAR PRIMARY KEY, -- REFERENCES titles (title_id),
  rating FLOAT,
  votes INTEGER
);

sqlite> .schema titles
CREATE TABLE titles (
  title_id VARCHAR PRIMARY KEY,
  type VARCHAR,
  primary_title VARCHAR,
  original_title VARCHAR,
  is_adult INTEGER,
  premiered INTEGER,
  ended INTEGER,
  runtime_minutes INTEGER,
  genres VARCHAR
);

まあ、映画？とかなのかね？そのキャストとか、レーティングとか、その辺だね。

QUESTION1

Find the 10 Sci-Fi works with the longest runtimes SF映画を上演時間が長い順に10個プリントしろって感じかな。その他の細かい指示としては、
PRINT THE ITLTE FO THE WORK, THE PREMIER DATA, AND THE RUNTIME.
The column listing the runtime should be suffixed with the string " (mins)
note a work is Sci-Fi even if it is categorized in multiple genres as long as Sci-Fi is one of the genres.

my ansqer query

sqlite> SELECT  DISTINCT (akas.title), titles.premiered,titles.runtime_minutes||' (mins)' from titles inner join akas on titles.title_id = akas.title_id where genres like '%Sci-Fi%' ORDER BY titles.runtime_minutes DESC ,akas.title asc limit 10;

The output

Cicak-Man 2: Planet Hitam|2008|999 (mins)
Cicak-man 2: The Black Planet|2008|999 (mins)
Cicakman 2 - Planet Hitam|2008|999 (mins)
Lizard Man|2008|999 (mins)
Project Hail Mary|2021|970 (mins)
Wholy|2018|660 (mins)
Tales from the Void|2016|600 (mins)
Blade Runner: Czy androidy marza o elektrycznych owcach? (Audioplay)|2012|403 (mins)
Blade Runner: Czy androidy marzą o elektrycznych owcach? (Audioplay)|2012|403 (mins)
Cold Lazarus|1996|300 (mins)

大事なんだけど、最初のSelectで

Q2

Determine the oldest people in the dataset who were born in or after 1900. You should assume that a person without a known death year is still alive.
Details: Print the name and age of each person. People should be ordered by a compound value of their age and secondly their name in alphabetical order. Return the first 20 results.

自信がないねー。

sqlite> SELECT people.name, (people.died - people.born) AS age FROM  people WHERE people.born >=1900 AND people.died != ''
Union 
SELECT people.name, (CURRENT_TIMESTAMP - people.born) AS age from people WHERE people.born >=1900 AND people.died = '' ORDER BY age DESC, name DESC LIMIT 20;
Richard Overton|112
Claude Choules|110
Vance Trimble|108
Rosa Bouglione|108
Hugues Cuénod|108
Abdul Rashid Khan|108
Norman Lloyd|107
Miklós Angelus|107
Marge Jetton|107
Manoel de Oliveira|107
Lucette Destouches|107
Henry Sharp|107
Edythe Kirchmaier|107
Nicholas Winton|106
Minoru Inuzuka|106
Sophie Templer-Kuh|105
Oscar Niemeyer|105
Licia Albanese|105
Dave McCoy|105
Buster Martin|105

ムズイ、一旦この課題はここまでにしておこうと思います。

この授業の問題はめっちゃいいので、消される前に残しておこうと思う

question2[5 points]

Find the 10 `Sci-Fi` works with the longest runtimes.
Details: Print the title of the work, the premiere date, and the runtime. The column listing the runtime should be suffixed with the string " (mins)", for example, if the runtime_mins value is `12`, you should output 12 (mins). Note a work is Sci-Fi even if it is categorized in multiple genres, as long as Sci-Fi is one of the genres.
Your first row should look like this: Cicak-Man 2: Planet Hitam|2008|999 (mins)

question3 [5 points]

Determine the oldest people in the dataset who were born in or after 1900. You should assume that a person without a known death year is still alive.
Details: Print the name and age of each person. People should be ordered by a compound value of their age and secondly their name in alphabetical order. Return the first 20 results.
Your output should have the format: NAME|AGE

question4[10 points]

Find the people who appear most frequently as crew members.
Details: Print the names and number of appearances of the 20 people with the most crew appearances ordered by their number of appearances in a descending fashion.
Your output should look like this: NAME|NUM_APPEARANCES

question5[10 points]

Compute intersting statistics on the ratings of content on a per-decade basis.
Details: Get the average rating (rounded to two decimal places), top rating, min rating, and the number of releases in each decade. Exclude titles which have not been premiered (i.e. where premiered is NULL). Print the relevant decade in a fancier format by constructing a string that looks like this: 1990s. Order the decades first by their average rating in a descending fashion and secondly by the decade, ascending, to break ties.
Your output should have the format: DECADE|AVG_RATING|TOP_RATING|MIN_RATING|NUM_RELEASES

question6[10 points]

Determine the most popular works with a person who has "Cruise" in their name and is born in 1962.
Details: Get the works with the most votes that have a person in the crew with "Cruise" in their name who was born in 1962. Return both the name of the work and the number of votes and only list the top 10 results in order from most to least votes. Make sure your output is formatted as follows: Top Gun|408389

question7 [15 points]

List the number of works that premiered in the same year that "Army of Thieves" premiered.
Details: Print only the total number of works. The answer should include "Army of Thieves" itself. For this question, determine distinct works by their title_id, not their names.

question8[15 points]

List the all the different actors and actresses who have starred in a work with Nicole Kidman (born in 1967).
Details: Print only the names of the actors and actresses in alphabetical order. The answer should include Nicole Kidman herself. Each name should only appear once in the output.
Note: As mentioned in the schema, when considering the role of an individual on the crew, refer to the field category. The roles "actor" and "actress" are different and should be accounted for as such.

question9[15 points]

For all people born in 1955, get their name and average rating on all movies they have been part of through their careers. Output the 9th decile of individuals as measured by their average career movie rating.
Details: Calculate average ratings for each individual born in 1955 across only the movies they have been part of. Compute the quantiles for each individual's average rating using NTILE(10).
Make sure your output is formatted as follows (round average rating to the nearest hundredth, results should be ordered by a compound value of their ratings descending and secondly their name in alphabetical order): Stanley Nelson|7.13
Note: You should take quantiles after processing the average career movie rating of individuals. In other words, find the individuals who have an average career movie rating in the 9th decile of all individuals.

question10[15 points]

Concatenate all the unique titles for the TV Series "House of the Dragon" as a string of comma-separated values in alphabetical order of the titles.
Details: Find all the unique dubbed titles for the new TV show "House of the Dragon" and order them alphabetically. Print a single string containing all these titles separated by commas.
Hint: You might find Recursive CTEs useful.
Note: Two titles are different even if they differ only in capitalization. Elements in the comma-separated value result should be separated with both a comma and a space, e.g. "foo, bar".

第三回

第一回と第二回で、データベースの概要と、データベースの論理的な動作を見た (クエリの話) この講義ではdisk based databaseについて講義する。disk basedっていうのは、データを二次記憶にためるって話。OK？で、レコード (tuple)をどうやって二次記憶にためるかって話なんだけど、まず、メモリにはバッファープールがあって、cpuがなにか操作を加えるためには、データはメモリ上にないとだめだよね。でも、メモリは有限だよね。だから、基本的には2次記憶にデータをためておいて、必要に応じてメモリに持ってくる。で、メモリに持ってくる単位をページっていうんだよね。で、ページにタプルを入れていくって話。ページにタプルをためていく方法だけど、slotted pageっていう方法がある。これは覚えておいた方がいいね。スロットと、実際のデータに分かれている。なぜslotted pageがいいかっていうと、tupleの長さは可変だからだね。これは覚えておこう。

一つだけ質問したいのだが、マルチスレッドで動いているプログラムのうちの一つのスレッドがシステムコールを呼び出して、カーネルモードになった時は、他のスレッドはないモードで動いているんでしょうかね？

第4回

slotted pageには問題があると。　

フラグメンテーション : tupleが削除された時にページに穴が開く
無駄なディスクi/o：tupleを一つだけ変更する為にもページをまるまる一つ持ってこないといけない
余分なディスクi/o：上と同じかな。変更したい複数のtupleが複数のページにまたがっているとき、複数のページすべてをメモリに読み込まないといけない。

そこで、log-structured storageっていうデータの保存方法もあるって話。これは、tupleのレコードだけを残しておくっていう方法。同じレコードidでもどんどん新しいものが追加されていくわけですね。ただ、無限に新しいものが追加されていっては2次記憶も圧迫される、ということで、定期的にcompactionと言って、ダブっているレコードを古い方から消していく、っていう方法が採用されている。で、ダブっているレコードを順番に並べたものをSSTableって言います。これは高速です。ただ、compactionが何回も実行されるのが良くないって話です。しかし、最近はこのlog-structured storageが使われるって話。

第5回

二次記憶にデータをためておくフォーマットが2つあるという話。ここまでは、ページにはタプルがまるまる一個入っているという構造だった。で、一つのページにはタプルが何個も入っている、といった感じ。これの格納方法は、N-Ary storage modelっていうもので、OLTPには適している。OLTPはデータのアップデートやインサートといったワークロードですね。しかし、この格納方式では、特定の列の集計処理をするといった、OLAPには適していない。なぜなら、ページを読み出すときに参照しないカラムまで読みだしてしまうことがあるからだ。そこで、tupleのカラムごとにページに保存して、二次記憶に書き出す、ていう方法が考えられたんだね。これが、decomposition storage model (DSM) っていう方法になります。これは、OLAPには適しているけど、OLTPには適していないって話。DSMでタプルを再構成するには、2つある。一番使われているのが、同じオフセットなら同じtupleっていう方法。

第6回

バッファープールマネージャーについての話。バッファープールマネージャーの仕事は、query executorからの指示を受けて、2次記憶から効率よくメモリ上にデータを持ってくること。バッファープールは、ページテーブルを持っているね。ページテーブルは、どのページがメモリ上のどこに置いてあるかを管理するテーブルだね。で、ページテーブルには、dirtybitを管理するフラグとかがあるって話。

で、効率よくページをフレームに持ってくるためにいくつか戦略があるって話なんだ。

シラバス#

第一回#

第二回#

aggregates#

String operation#

Pattern Matcing#

String functions#

Concatination#

Date and Time#

Output Redirection#

Output Control#

Nested Queries#

ASSIGNMENT#

QUESTION1#

my ansqer query#

Q2#

この授業の問題はめっちゃいいので、消される前に残しておこうと思う#

question2[5 points]#

question3 [5 points]#

question4[10 points]#

question5[10 points]#

question6[10 points]#

question7 [15 points]#

question8[15 points]#

question9[15 points]#

question10[15 points]#

第三回#

第4回#

第5回#

第6回#