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/13/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 in a text file
- use the psql tool to execute the inserts
Use pg_dump -Fc mydb > dump.db to export the db.
Reference «here».
Method B should be easier to do and test.
Submission
- 1 file dump.db - 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.
How to install Postgres: see official «documentation»
How we would install it on Linux (The steps bellow are for Fedora 33, but should work similarly for previous versions):
sudo -s
# DB Setup
dnf install postgresql postgresql-server
postgresql-setup initdb
systemctl start postgresql
# DB and User Creation
su - postgres
createuser <username> -P # <username> of your linux account - saves you from configuring access to your database in the pg_hba.conf if using a different username.
createdb <db-name> --owner=<username> # <db-name> can be any arbitrary database name.
exit # postgres
exit # root
# Postgres -CLI
psql -d <db-name> -U <username> # interactive console where you can write SQL. You can omit -U if you've used your account's username.
# When your are done with your assignment, you can dump your database with
pg_dump -Fc <db-name> > dump.sql
Letzte Änderung: 01.04.2021, 12:43 | 591 Worte