Please disable Adblockers and enable JavaScript for domain CEWebS.cs.univie.ac.at! We have NO ADS, but they may interfere with some of our course material.
[Info]
Assignment 2
Create a postgres SQL database for your model and fill each table with at least 777 rows.
Include the following data types in your tables («https://www.postgresql.org/docs/8.4/static/datatype.html»):
- at least 4 attributes of type integer (any integer)
- at least 4 attributes of type varchar
- at least 2 attributes of type timestamp (or date or time)
- at least 4 attributes of type float
- at least 2 attributes of type xml
The XML in the xml columns hast to contain at least 3 levels of elements (including root element) and at least 2 different attributes. E.g.:
<address> <-- level 1-->
<name>
<given_name>Chuck</given_name>
<family_name>Norris</family_name>
</name>
<address zip="1190" country="AT">
<street> <-- level 3-->
<name>Waehringerstrasse</name> <-- level 4-->
<number type="main">29</number>
<number type="internal">4</number>
<number type="internal">4.49</number>
</street>
<city>Vienna</city>
</address>
</address>
The database has to be in 3rd normal form. Please change the data model to fulfill the above requirements and submit the changed data model.
The contents of the tables should be random, but make sense. I will be pleased if the contents are funny. Tips to create the content:
- Method A: create a PHP script that connects to the database, does all the "create table" statements, and all the "insert" statements
- Method B:
- create the tables in the psql tool, go back to command line
- create a small program that creates all the "insert" statements
- apply the inserts to the database
- use pg_dump mydb > dump.sql to export the db
Method B should be easier to do and test.
Submission
- 1 file dump.sql - created with pg_dump command line tool than can be imported with pg_restore.
- 1 file doc.pdf which contains the (revised) data model picture.
- 1 file code.zip which contains the source code of the file that created the tables and their content (PHP cli program should be simple enough)
Hints
- If the filenames are not correct, you will get no points.
- If your dump can not be imported with pg_restore (TEST IT!), you will get no points.
- If your model/db does not contain the required data types you will get no points.
- If your model is not correct you will get no points.
- If your model does not match the database, you get no points.
- Without a correct database all subsequent assignments can not be done.
Just a hint what I did to install Postgres and get started:
sudo -s
dnf install postgresql postgresql-server
postgresql-setup --initdb
service postgresql start
su - postgres
createuser mangler -P
createdb --owner=mangler mydb
exit
exit
psql # interactive console where I wrote create table ...
pg_dump mydb > dump.sql
Letzte Änderung: 22.03.2017, 10:09 | 488 Worte