Needing help creating a datamodel from the below specifications.

Relationships I believe will be needed as tables are: user, bookmarks, subscriptions, tags, and cache.

The specifications state that the tables be in 4th normal form.

The system has multiple users, each of which has a username, a password hash, an e-mail address, and an account create date.

Users store bookmarks. Each bookmark has a URL, title, description, create date update date, a private flag, and zero or more tags.

Each bookmark may have an associated cache. The cache stores the cache date, the result of the cache operation (success, not found, server failed), and if successful, the contents of the web page that was cached.

Users can subscribe to other users bookmark feeds. The idea is that the system would have a news feed, where users can see what non-private bookmarks have been added by the users whose feeds they subscribe to.

1.Most confusing parts: bookmarks have 0 or more tags is this serial default 0? (we're using postgresql)

2.Tags attribute, or seperate table.

3. composite attribute in cache table for for result (success , not found, server failed) how to or seperate table?

sorry this is so long, but trying to be thorough, any help is appreciated really want to get the tables right so that I can focus on getting the queries right. Seeing how someone else might do it will be helpful for me. full project specifications after table, maybe looking at some of the query requests will help, they're scaring me.

user	bookmarks	tags	subscribesTo	cashes
user_id (serial) PK	mark_id (serial) PK	tagID (serial) PK	user_id FK	cache_id (serial) PK
user_name (varchar)	URL (varchar)	mark_id FK	mark_id FK	cache_date (date)
password (varchar) md5?	user_id FK	user_id FK	PK(user_id, mark_id_	result (multi valued?)
email (varchar)	tag_id FK	tag (varchar)		contents (BYTEA) or blob
creation_date (date	cache_id FK
	flag (boolean)
	created (date)
	updated (date)
	title (varchar)
	discription (varchar)

Whole project:

Assignment 3: Data to SQL CS 410/510 - Databases Due Sep. 23, 2018 at 11:59 p.m. Summary In this assignment, you will design a data model for storing bookmarks, write the SQL DDL, and write queries against it. You should submit two files: the results of your assignment as a single PDF file, and a dump of your database as a .sql file. The PDF file must include your SQL DDL (CREATE TABLE statements, etc.). Problem Setup This problem is to develop the data model for a bookmark site like Pinboard or Delicious.

The system has multiple users, each of which has a username, a password hash, an e-mail address, and an account create date.

Users store bookmarks. Each bookmark has a URL, title, description, create date, update date, a private flag, and zero or more tags.

Each bookmark may have an associated cache. The cache stores the cache date, the result of the cache operation (success, not found, server failed), and if successful, the contents of the web page that was cached.

Users can subscribe to other users bookmark feeds. The idea is that the system would have a news feed, where users can see what non-private bookmarks have been added by the users whose feeds they subscribe to.

Part 1 (20 points): Data Model Draw an E-R model for the data needed for this problem.

Part 2 (30 points): SQL DDL Write SQL CREATE TABLE statements to implement your model. Include foreign key relationships. Your database must be in the 4th Normal Form.

Part 3 (10 points): Populate the Database Populate your database with some example data. Mockeroo is a useful tool for generating random data to put in a database, and it can create SQL INSERT statements.

Part 4 (40 points): SQL Queries Write SQL queries to retrieve the following data.

1. For a specific user (pick one), their bookmarks in order of date created, newest first.

2. The 10 users with the most bookmarks.

3. The number of bookmark caches with each status.

4. For a specific user, their news feed: the 100 most recently created bookmarks by the people they subscribe to. Include tags, joined with spaces (the string_agg function is useful for this).

5. The number of new users each year.

6. The number of users with more than 10,000 bookmarks.

7. The number of cached pages each user has.

8. The number of subscribers each user has.

9. For a specific user, the tags they have used along with the number of bookmarks associated with each.

10. For a specific user and a specific tag, all bookmarks they have saved with that tag.

Show transcribed image text