Question

Create a Database in POSTGRESQL using the following table names and attributes:

• users: userid (int, primary key), name (text)
• movies: movieid (integer, primary key), title (text)
• taginfo: tagid (int, primary key), content (text)
• genres: genreid (integer, primary key), name (text)
• ratings: userid (int, foreign key), movieid (int, foreign key), rating (numeric), timestamp (bigint, seconds since midnight Coordinated Universal Time (UTC) of January 1, 1970)
• tags: userid (int, foreign key), movieid (int, foreign key), tagid (int, foreign key), timestamp (bigint, seconds since midnight Coordinated Universal Time (UTC) of January 1, 1970).
• hasagenre: movieid (int, foreign key), genreid (int, foreign key)

Answer 1

Syntax create database

create database database_name;

CREATE DATABASE DemoDatabase;

Syntax use database

\c database_name;

\c DemoDatabase;

Users Table

Syntax create table

create table table_name (

column1 datatype(length) column_contraint,

column2 datatype(length) column_contraint,

column3 datatype(length) column_contraint,

table_constraints

);

CREATE TABLE users(

userid int PRIMARY KEY,
name TEXT
);

Movies Table

  create table table_name (

column1 datatype(length) column_contraint,

column2 datatype(length) column_contraint,

column3 datatype(length) column_contraint,

table_constraints

);

CREATE TABLE movies(

movieid integer PRIMARY KEY,
title TEXT
);

taginfo Table

create table table_name (

column1 datatype(length) column_contraint,

column2 datatype(length) column_contraint,

column3 datatype(length) column_contraint,

table_constraints

);

CREATE TABLE taginfo(

tagid int PRIMARY KEY,
content TEXT
);

genres Table

create table table_name (

column1 datatype(length) column_contraint,

column2 datatype(length) column_contraint,

column3 datatype(length) column_contraint,

table_constraints

);

CREATE TABLE genres(

genreid integer PRIMARY KEY,
name TEXT
);

ratings Table

create table table_name (

column1 datatype(length) column_contraint,

column2 datatype(length) column_contraint,

column3 datatype(length) column_contraint,

table_constraints

);

CREATE TABLE ratings(

userid int,
movieid int,
rating numeric,
timestamp bigint default (extract(epoch from now()) * 1000),
FOREIGN KEY (userid) REFERENCES users (userid),
FOREIGN KEY (movieid) REFERENCES movies (movieid)
);

tags Table

create table table_name (

column1 datatype(length) column_contraint,

column2 datatype(length) column_contraint,

column3 datatype(length) column_contraint,

table_constraints

);

CREATE TABLE tags(

userid int,
movieid int,
tagid int,
timestamp bigint default (extract(epoch from now()) * 1000),
FOREIGN KEY (userid) REFERENCES users (userid),
FOREIGN KEY (movieid) REFERENCES movies (movieid),
FOREIGN KEY (tagid) REFERENCES taginfo (tagid)
);

hasagenre Table

create table table_name (

column1 datatype(length) column_contraint,

column2 datatype(length) column_contraint,

column3 datatype(length) column_contraint,

table_constraints

);

  CREATE TABLE hasagenre(

movieid int,
genreid int,
FOREIGN KEY (movieid) REFERENCES movies (movieid),
FOREIGN KEY (genreid) REFERENCES genres (genreid)
);

==Please Upvote==