03 - 02 - Rhythm's gonna get you - ERD

[Agent-Banks]

Rhythm's gonna get you - ERD

For this project, we will model and create a database. We are starting a record label company, and we a place to store our bands, albums, and eventually songs. You are creating a console app that stores our information in a database.

Objectives

• Practice creating an Entity Relationship Diagram
• Practice working with SQL

Requirements

Create the ERD (Entity Relationship Diagram) and resulting tables that allows a user to store and manage the company's bands, albums, and (eventually) songs.

How to turn in this assignment.

1. If you draw your ERD on paper, attach a photo. If you draw your ERD in a software tool, attach a screenshot of the image.
2. Paste all the SQL you wrote to create tables, insert rows, and try out SELECT/JOIN statements as a comment to this assignment.

Explorer Mode

• [x] Create an ERD for Albums, and Bands. They should have the following properties, use your best judgment for types.

  • [x] Album

  • Id

  • Title

  • IsExplicit

  • ReleaseDate

  • [x] Band

  • Id

  • Name

  • CountryOfOrigin

  • NumberOfMembers

  • Website

  • Style

  • IsSigned

  • ContactName

  • ContactPhoneNumber

  • Add ERD relationships that show:

  • [x] One Band has many Albums

  • Create SQL statements that:

  • [x] Add a new band

  • [x] View all the bands

  • [x] Add an album for a band

  • [x] Let a band go (update isSigned to false)

  • [x] Resign a band (update isSigned to true)

  • [x] Given a band name, view all their albums

  • [x] View all albums ordered by ReleaseDate

  • [x] View all bands that are signed

  • [x] View all bands that are not signed

ALTERNATIVES

• If you do not wish to take on Band + Album you may go back to any of these assignments with an equivalent effort:
  • First Bank of Suncoast - Generate an ERD that supports multiple Users. Users have "Name", "Address", "Phone Number" - There will also be Transactions, each transaction will have a type (Deposit or Withdraw) and an account Checking or Savings. Generate the ERD and demonstrate SQL to create the tables, insert users, make transactions for users.
  • Jurassic Park - Generate an ERD that supports the existing Dino as well as an entity for Enclosures. The Enclosure should keep track of its Name, Capacity (number of Dinos), Location in the park. The Dinos should be able to be assigned to an enclosure. Generate the ERD and demonstrate SQL to create the tables, insert enclosures, insert Dinos, transfer dinos from one enclosure to another.

[Agent-Banks]

--1.

CREATE TABLE "Bands" ( "Id" SERIAL PRIMARY KEY, "Name" TEXT NOT NULL, "CountryOfOrign" TEXT, "NumberOfMemebers" INT, "Website" TEXT, "Style" TEXT, "IsSigned" BOOLEAN, "ContactName" TEXT, "ContactPhoneNumber" TEXT );

CREATE TABLE "Albums" ( "Id" SERIAL PRIMARY KEY, "BandId" INTEGER REFERENCES "Bands" ("Id"), "Title" TEXT NOT NULL, "IsExplicit" BOOLEAN, "ReleaseDate" DATE );

--2.

INSERT INTO "Bands" ( "Name", "CountryOfOrign", "NumberOfMemebers", "Website", "Style", "IsSigned", "ContactName", "ContactPhoneNumber") VALUES ('Led Zeppelin', 'United Kingdom', '4', 'ledzeppelin.com', 'Rock', 'True', 'Bill', '727-765-0978');

--3.

SELECT * FROM "Bands";

--4.

INSERT INTO "Albums" ("BandId", "Title", "IsExplicit", "ReleaseDate") VALUES ('1', 'Houses of The Holy', 'False', '1973-03-28');

--5.

UPDATE "Bands" SET "IsSigned" = 'False' WHERE "Name" = 'Led Zeppelin';

--6.

UPDATE "Bands" SET "IsSigned" = 'True' WHERE "Name" = 'Led Zeppelin';

--7. --Select "Employees"."FullName", "Departments"."Id" --From "Employees" --Join "Departments" ON "Employees"."DepartmentId" = "Departments"."Id";

SELECT "Albums"."Title", "Bands"."Name" FROM "Albums" JOIN "Bands" ON "Albums"."BandId" = "Bands"."Id";

--8.

Select * FROM "Albums" ORDER BY "Albums"."ReleaseDate";

--9.

SELECT * FROM "Bands" WHERE "IsSigned" = 'True';

--10.

SELECT * FROM "Bands" WHERE "IsSigned" = 'False';

[Agent-Banks]

Your homework 03 - 02 - Rhythm's gonna get you - ERD was marked: Meets Expectations

“Well done!”