# Programming Assignment 1
## Gruppe
Philipp Schönemann
* Matrikelnummer: 4435998
* E-Mail: philipp8@uni-bremen.de
Mike Schöning
* Matrikelnummer: 4467286
* E-Mail: MikeSchoening@uni-bremen.de
## Part B:
Die Prioritäten des nachfolgenden Datenbankmodells sind vorallem ***Absicherung gegenüber Anomalien*** und ***Redundanzvermeidung***. Dies führt zu einer höheren Anzahl von Tabellen, was unter Umständen Performanz-Einbußen bei SQL-Abfragen mit sich bringt.
### Relation: Users
| id |
| ------- |
| VARCHAR |
**Primärschlüssel: id**
**Nicht-triviale funktionale Abhängigkeiten:**
* Keine vorhanden
Enthält alle Benutzer.
Da diese Relation nur ein einziges Attribut enthält (was unschön ist), könnte man theoretisch die Benutzer-ID direkt in den anderen Tabellen benutzen und diese Relation gänzlich löschen. Allerdings bietet die derzeitige Variante einige Vorteile:
(1) Die Benutzer zentral gespeichert.
(2) Ermöglicht den Einsatz von Fremdschlüsseln, was das Bearbeiten der Datensätze erleichtert.
### Relation: Items
| id | name | first_bid | location | start_time | end_time | seller_id | description | country_id |
| ------ | ------- | ------------ | -------- | ---------- | --------- | --------- | ------------- | ---------- |
| BIGINT | VARCHAR | DECIMAL(8,2) | VARCHAR | TIMESTAMP | TIMESTAMP | VARCHAR | VARCHAR(4000) | BIGINT |
**Primärschlüssel: id**
**Nicht-triviale funktionale Abhängigkeiten:**
* id -> name
* id -> first_bid
* id -> location
* id -> start_time
* id -> end_time
* id -> seller_id
* id -> description
* id -> country_id
Enthält alle Auktionen (sowohl laufende als auch abgeschlossene) und deren relevante Daten.
Die Relation ist in BCNF. Man kann argumentieren, dass **location** funktional abhängig von **country_id** ist, da jeder Ort einem Land zugeordnet werden kann und die Relation nur in 2NF ist. Allerdings sind in einigen Auktionen keine echten Orte bzw. Spaßbezeichnungen angegeben, sodass diese Abhängigkeit nicht mehr gegeben ist. Aus diesem Grund ist **location** eher von **id** abhängig.
Zusätzlich haben wir hier den Wert von Currently nicht übernommen, da dieser mithilfe der vorhandenen Informationen reproduziert werden kann. Auch die Anzahl der Bids haben wir nicht in der Datenbank gespeichert, da diese ebenfalls reproduziert werden können.
### Relation: BuyNowPrices
| item_id | price |
| ------- | ------------ |
| BIGINT | DECIMAL(8,2) |
**Primärschlüssel: item_id**
**Nicht-triviale funktionale Abhängigkeiten:**
* item_id -> price
Da ein Angebot *keinen oder genau einen* Sofortkauf-Preis besitzen kann, wird dieser in einer eigenen Relation gespeichert.
Der Vorteil ist, dass ein Sofortpreis hinzugefügt oder entfernt werden kann, ohne, dass andere Tabellen verändert werden müssen. Ein Nachteil ist, dass in einem Query beide Relationen zusammengefügt werden müssen.
### Relation: Categories
| id | name |
| ------ | ------- |
| BIGINT | VARCHAR |
**Primärschlüssel: id**
**Nicht-triviale funktionale Abhängigkeiten:**
* id -> name
Die Kategorien werden ebenfalls in einer eigenen Relation gespeichert, da viele Angebote auf Schnittmengen von Kategorien verweisen. Bei Änderung einer Kategoriebezeichnung muss diese nur hier umbenannt werden.
### Relation: ItemCategories
| item_id | category_id |
| ------- | ----------- |
| BIGINT | BIGINT |
**Primärschlüssel: item_id & category_id**
**Nicht-triviale funktionale Abhängigkeiten:**
* Keine
Eine Cross-reference Relation, da zwischen Auktionen und Kategorien eine n:n-Beziehung vorliegt.
### Relation: Bids
| item_id | bidder_id | time | amount |
| ------- | ----------- | --------- | ------------ |
| BIGINT | VARCHAR | TIMESTAMP | DECIMAL(8,2) |
**Primärschlüssel: item_id & bidder_id**
**Nicht-triviale funktionale Abhängigkeiten:**
* {item_id, bidder_id} -> time
* {item_id, bidder_id} -> amount
* {item_id, bidder_id, amount } -> time
* {item_id, bidder_id, time } -> amount
Hier wurde auf **bid_id** als Primärschlüssel verzichtet, da bereits die beiden Attribute **item_id** und **bidder_id** ein Gebot eindeutig identifizieren. Dies ist in diesem Fall möglich, weil nur das neuste Gebot eines Benutzers gespeichert wird.
### Relation: UserCountries
| user_id | country_id |
| ------- | ---------- |
| VARCHAR | BIGINT |
**Primärschlüssel: user_id**
**Nicht-triviale funktionale Abhängigkeiten:**
* user_id -> country_id
Da ein Benutzer sein Land *angeben oder nicht angeben* kann, wird dieser in einer eigenen Relation gespeichert.
### Relation: Countries
| id | name |
| ------ | ------- |
| BIGINT | VARCHAR |
**Primärschlüssel: id**
**Nicht-triviale funktionale Abhängigkeiten:**
* id -> name
Den Ländern wurde eine eindeutige ID gegeben, da es sehr viele Benutzer gibt aber nur rund 200 Länder. Daher macht es mehr Sinn von den anderen Relationen per ID auf das Land zu verweisen. Auf diese Weise muss eine fehlerhafte Länderbezeichnung nur ein einziges Mal zentral geändert werden.
### Relation: BidderRatings
| user_id | rating |
| ------- | ------ |
| VARCHAR | BIGINT |
**Primärschlüssel: user_id**
**Nicht-triviale funktionale Abhängigkeiten:**
* user_id -> rating
Da ein Benutzer nur dann eine Bieter-Bewertung besitzt, wenn er bei mindestens einer Auktion gebietet hat, wird die Bewertung in einer eigenen Relationen gespeichert, die eindeutig mit Hilfe der **user_id** identifiziert wird. So kann ein Benutzer keine (er ist lediglich Verkäufer) oder genau eine Bieter-Bewertung haben.
### Relation: SellerRatings
| user_id | rating |
| ------- | ------ |
| VARCHAR | BIGINT |
**Primärschlüssel: user_id**
**Nicht-triviale funktionale Abhängigkeiten:**
* user_id -> rating
Da ein Benutzer nur dann eine Verkäufer-Bewertung besitzt, wenn er mindestens eine Auktion eröffnet hat, wird die Bewertung in einer eigenen Relationen gespeichert, die eindeutig mit Hilfe der **user_id** identifiziert wird. So kann ein Benutzer keine (er ist lediglich Bieter) oder genau eine Verkäufer-Bewertung haben.
### Relation: Coordinates
| item_id | latitude | longitude |
| ------- | ------------ | --------- |
| BIGINT | DECIMAL | DECIMAL |
**Primärschlüssel: item_id**
**Nicht-triviale funktionale Abhängigkeiten:**
* item_id -> latitude
* item_id -> longitude
* { item_id, longitude } -> latitude
* { item_id, latitude } -> longitude
Der Ort kann optional mit Hilfe von geographischen Koordinaten näher bestimmt werden. Da das allerdings nur für Auktionen möglich sein soll und nicht für Benutzer, werden die Koordinaten mit der **item_id** eindeutig identifiziert. Weil die Koordinaten optional sind, werden diese in einer eigenen Relation gespeichert und nicht **Items** direkt. Zwar wäre dies mit NULL-Werten ebenfalls möglich, aber es wurde sich dagegen entschieden, da fehlerhafte Zustände enstehen könnten (Nur Latitude oder nur Longitude ist gesetzt). Zudem erschweren NULL-Werte allgemein Queries.
### Relation: Locations
| user_id | location |
| ------- | -------- |
| VARCHAR | VARCHAR |
**Primärschlüssel: user_id**
**Nicht-triviale funktionale Abhängigkeiten:**
* user_id -> location
Anders als bei den Ländern haben wir uns bei den Orten dagegen entschieden, jedem Ort eine ID zu vergeben, da bei einigen Auktionen Spaßnamen oder nicht existente Orte angegeben wurden.
## Part E:
### 1
```sql
SELECT COUNT(*) AS user_count FROM Users;
```
<!-- 13422 -->
### 2
```sql
SELECT COUNT(*) AS items_count_new_york FROM Items
WHERE location LIKE BINARY "New York";
```
<!-- 103 -->
### 3
```sql
SELECT COUNT(*) AS count_items_four_categories FROM
(SELECT item_id FROM ItemCategories GROUP BY item_id HAVING COUNT(*) = 4) items_four_categories;
```
<!-- 8372 -->
### 4
```sql
SELECT id FROM Items
WHERE id IN (SELECT DISTINCT item_id FROM
Bids AS b1
JOIN
(SELECT MAX(amount) AS highest_bid FROM Bids) AS b2
ON b1.amount = b2.highest_bid)
AND end_time > '20-12-2001 00:00:01';
```
### 5
```sql
SELECT COUNT(*) AS high_rated_sellers_count FROM SellerRatings
WHERE rating > 1000;
```
<!-- 3130 -->
### 6
```sql
SELECT COUNT(*) AS seller_aswell_as_bidder_count FROM SellerRatings
INNER JOIN BidderRatings ON SellerRatings.user_id = BidderRatings.user_id;
```
<!-- 6717 -->
### 7
```sql
SELECT COUNT(*) FROM (
SELECT DISTINCT category_id FROM ItemCategories
WHERE item_id IN(SELECT DISTINCT item_id FROM Bids WHERE amount > 100)
) AS x;
```
<!-- 150 -->
Sign in with Wallet
Connect another wallet