Create an SQL table and schema and explain any constraints.

Question:

Create an SQL table and schema and explain any constraints ( eg. An album may be by an artist or a band (but not both)).

Each artist has a unique ID, a name, and an address. A band has a name, year established, and place of origin. A band has several artists. An artist usually is a member of only one band at a time. Each song has an ID and a title. Genres of songs are described by a unique name, some description. For example, “rock” is a genre with the description “rock music originated as ‘rock and roll’ in the United States”.

A genre may have many songs. Each album recorded on the XB label has a title, a release date, a format (such as CD, MP3), and a unique album number. Each album has many tracks on it (up to 20 usually), and they are numbered sequentially. Each track of an album usually corresponds to a song. An album may be by an artist or a band (but not both). A song may appear on several albums. A song has one or more genres, and a genre may have many songs.

Examples for queries on the database:

Look up the details of artists, bands, songs, and albums. List the songs and their track number for the album “Thriller” (title). Give the number of songs for each genre. Find the albums by an artist named “ Bob Janes”.Give the name of the band for the album “The Last Waltz”.How many albums have the song “Almost is Never Enough”?

 

Summary:

SQL (Structured Query Language) is a standard language for dealing with relational database management systems. It is used to perform operations like C(Create). R(Retrieve). U(Update). D(Delete) data. SQL is used to design and create an SQL tables and schema. It also creates and manages the databases. SQL is also used to perform administration tasks such as security, user management, import/export, etc…

SQL is a hybrid language, and it is categorized into four types

 1)DDL-Data Definition Language:

Commands:

1. Create
2. Alter
3. Rename
4. Drop
5. Truncate

 

2) DML-Data Manipulation Language:

Commands:

1. Insert
2. Select
3. Update
4. Delete

 

3) DQL-Data Query Language:

Command:

1. Select

 

4)DCL-Data Control Language:

Commands:

1. Grant
2. Revoke

 

EXPLANATION STEPS TO CREATE AN SQL TABLE AND SCHEMA:

For creating an SQL table and schema,

firstly, we must need to create tables for Artist, Band, Album, Song, Genre with required primary key variables and also with specified datatypes as mentioned below.

After that by using DQL command SELECT, we need to do various operations to get the respected outputs.

TABLE ARTIST :                                         

FIELD	TYPE
aid	int
name	varchar[10]
address	varchar[20]

 TABLE SONG:

FIELD	TYPE
sid	int
tittle	varchar[30]
tno	int

TABLE BAND :                                           

FIELD	TYPE
name	varchar[10]
year	Year
origin	varchar[10]

TABLE GENRE:

FIELD	TYPE
Sid	int
gname	varchar[10]
description	varchar[20]

TABLE ALBUM:

FIELD	TYPE
albid	int
tittle	varchar[30]
date	date
aformat	char[3]
sid	int
aid	int
bname	varchar[10]

The required database SQL schema is:

Program:

CREATE TABLE Artist
(   aid int primary key,
    name varchar[10],
    address varchar[20],
    aname varchar[10]
);
    
CREATE TABLE Band
(   bname varchar[10],
    year  year,
    Origin varchar[10]
);

CREATE TABLE Song
(   sid int primary key,
    title   varchar[30],
    tno int
);

CREATE TABLE Genre
(   sid int ,  
    gname varchar[10],
    Description varchar[20]
);

CREATE TABLE Album
(   albid int primary key,
    title varchar[30],
    date date,
    aformat char[3],
    sid int,
    aid int,
    bname varchar[10]
);

SELECT * from Artist;
SELECT * from Band;
SELECT * from Song,Genre where Song.sid=Genre.sid;
SELECT * from Album;

SELECT tno, Song.title from Song, Album 
WHERE Album.title='Thriller';

SELECT count(sid),gname from Genre
group by gname;

SELECT title from Album,Artist 
WHERE Album.aid=Artist.aid
and aname='Bob Janes';

SELECT bname from Album 
WHERE title='The last Waltz';

SELECT Count(distinct albID) from Album, Song
WHERE Album.sid=(SELECT sid from Song WHERE title='Almost is Never Enough');

Output:

Also, read our other blog which is Write a program for the customer who is willing to take the vehicles for rent.