Music Database

Introduction to optimization
Database optimization is the strategy of reducing database system response time.
Databases provide us with information stored with a hierarchical and related structure, which allows us to
extract the content and arrange it easily. There are plenty of options to choose from for business and
companies. Even though MySQL is the most widespread database, there are other alternatives, such as
Microsoft SQL Server, PostgreSQL, or MongoDB.
Regardless of our choice, there are a number of common recommendations regarding the management
and optimization of the databases. Following these best practices will make your work easier, improving
the experience of our users.
How to Practice Database Optimization
1. Avoid Unused Tables
No matter how carefully you plan the initial approach to the database, as time goes by, you inevitably end
up with unused tables. Just do not hesitate: delete them! Having unused tables is quite similar to keeping
things you don’t use in a full drawer. When you go looking for something, it can take you much longer to
find it! The same thing happens in the databases: the systems have to track all the tables and elements
until they find the answer to the query.
2. Proper Indexing
Having a good index among tables is essential for relational searches to work correctly. Add indexes to
the tables and use the query statements (SELECT, WHERE …). It is also advisable to periodically check
the registry of slow queries to identify those that should be optimized. No indexing at all or excessive
indexing are not a good idea. Without any indexing, the process will be prolonged, whereas indexing
everything will render the insert and update triggers ineffective
3. Avoid Temporary Tables and Coding Loops
If any a code can be well written simply, there is absolutely no need to make it complex with temporary
tables. Subqueries usually alternate temporary tables, but keep in mind the precise performance that each
of these would provide in each case.
Avoiding coding loops is much required in order to prevent stalling the entire sequence. It can be
accomplished by employing the unique UPDATE or INSERT commands with individual rows and by
making sure that the WHERE command does not update the stored data in case it finds a preexisting
matching data.
Music Database Tables
Artist
Artist_ID F_Name L_name
Create table Artist (
Artist_Id varchar (10) not Null,
F_Name varchar (20) not Null,
L_name varchar (10) not Null,
Constraint Artist_pk primary key (Artist_ID)
);
Album
Album_no Album_name Relaese_date Artist_Id

Create table Artist (
Album_no varchar (10) not Null,
Album_name varchar (10) not Null,
Relaese_date varchar (10) not Null,
constraint Artist_pk primary key (Album_no),
constraint Music_Made_fk foreign key (Album_no) references Album (Album_no)
);
Track
Track_no name Genre Time_length Album_No
Create table Track (
Track_no varchar (10) not Null,
name varchar(20) not Null,
Genre varchar (10) not Null,

Time_length varchar (10) not Null,
Album_No varchar (10) not Null,
constraint Track_pk primary key (Track_no),
constraint Track_fk foreign key (Album_no) references Album (Album_no)
);
Played
P_time P_date Track_no Album_no

Create table Played (
P_time_no varchar (10) not Null,
P_date varchar(10) not Null,
Album_no varchar (10) not Null,
constraint Played_fk foreign key (Album_no) references Album (Album_no),
constraint Played_fk foreign key (Track_no) references Track (Track_no)
);
User
SSN Name Email password

Create table User (
SSN varchar (10) not Null,
Name varchar(20) not Null,
Email varchar (20) not Null,
Constraint User_pk primary key (SSN)
);
Favorite
Track_no SSN
Create table Favorite (
constraint Favorite_fk foreign key (Track_no) references Track (Track_no),
constraint Favorite_fk foreign key (SSN) references User (SSN)
);
Download
Track_no SSN
Create table Download (
constraint Download_fk foreign key (Track_no) references Track (Track_no),
constraint Download_fk foreign key (SSN) references User (SSN)
);
Query optimization
Select F_name, Track.name
Where Time_length > 3 min ,
Genre = “slow”,
Album_no = Track.Album_no,
Artist_Id = Album.Artist_Id,
From Track, Artist, Album;

Step 1
Ꙥ F_name, Track.name
σ Genre = “slow”, Album_no = Track.Album_no, release_date >2000, Artist_Id = Album.Artist_Id
Artist
x
Track Album
Step 2:
Ꙥ F_name, Track.name
, release_date >2000, Artist_Id = Album.Artist_Id
Artist
x
σ Genre = “slow”
Album
Track

Music Database

Uploaded by

Copyright:

Available Formats

Music Database

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Music Database

Uploaded by

Copyright:

Available Formats

Introduction to optimization

Database optimization is the strategy of reducing database system response time.

How to Practice Database Optimization

1. Avoid Unused Tables

3. Avoid Temporary Tables and Coding Loops

Artist_ID F_Name L_name

Create table Artist (

Artist_Id varchar (10) not Null,

F_Name varchar (20) not Null,

L_name varchar (10) not Null,

Constraint Artist_pk primary key (Artist_ID)

Album_no Album_name Relaese_date Artist_Id

Album_no varchar (10) not Null,

Album_name varchar (10) not Null,

Relaese_date varchar (10) not Null,

constraint Artist_pk primary key (Album_no),

constraint Music_Made_fk foreign key (Album_no) references Album (Album_no)

Track_no name Genre Time_length Album_No

Create table Track (

Track_no varchar (10) not Null,

name varchar(20) not Null,

Genre varchar (10) not Null,

Album_No varchar (10) not Null,

constraint Track_pk primary key (Track_no),

constraint Track_fk foreign key (Album_no) references Album (Album_no)

P_time P_date Track_no Album_no

P_time_no varchar (10) not Null,

P_date varchar(10) not Null,

Track_no varchar (10) not Null,

Album_no varchar (10) not Null,

constraint Played_fk foreign key (Album_no) references Album (Album_no),

constraint Played_fk foreign key (Track_no) references Track (Track_no)

SSN Name Email password

SSN varchar (10) not Null,

Name varchar(20) not Null,

Email varchar (20) not Null,

Constraint User_pk primary key (SSN)

Track_no varchar (10) not Null,

SSN varchar (10) not Null,

constraint Favorite_fk foreign key (Track_no) references Track (Track_no),

constraint Favorite_fk foreign key (SSN) references User (SSN)

Track_no varchar (10) not Null,

SSN varchar (10) not Null,

constraint Download_fk foreign key (Track_no) references Track (Track_no),

constraint Download_fk foreign key (SSN) references User (SSN)

Select F_name, Track.name

Where Time_length > 3 min ,

From Track, Artist, Album;

σ Genre = “slow”, Album_no = Track.Album_no, release_date >2000, Artist_Id = Album.Artist_Id

, release_date >2000, Artist_Id = Album.Artist_Id

You might also like