I need the information turned into logical data. Develop a logical data model based on the following requirements: • Refinement of the conceptual model - including a refined Enhanced Entity-Relationship (EER) diagram. • Derivation of relations from the refined conceptual model. • Validation of logical model using normalization to BCNF. • Validation of logical model against corresponding user transactions. • Definition of integrity constraints including: – Primary key constraints. – Referential Integrity (foreign key) constraints. – Entity integrity (NULL and default value) constraints. – Alternate key constraints. – General constraints if any Relationship types: Plays: between Musician and Instrument, with participation constraints (0, N) on Musician and (0, N) on Instrument. AppearsOn: between Song and Musician, with participation constraints (1, N) on Song and (1, N) on Musician. Contains: between Album and Song, with participation constraints (1, N) on Album and (1, N) on Song. Produces: between Musician and Album, with participation constraints (1, N) on Musician and (1, 1) on Album. UsedIn: between Instrument and Song, with participation constraints (1, N) on Instrument and (1, N) on Song. Attributes: Musician: SSN (primary key), name, address, phone number. Instrument: name (primary key), musical key. Album: album identifier (primary key), title, copyright date, format. Song: title (primary key), author. Producer: none. Entity types: Musician Instrument Album Song Producer Assumptions: A musician can only have one SSN. An instrument can only have one name. An album can only have one album identifier. A song's title is unique and cannot appear on more than one album. Each album must have exactly one producer. Sample queries: INSERT INTO Musicians (SSN, Name, Address, Phone) VALUES ('123-45-6789', 'John Smith', '123 Main St, Anytown, USA', '555-555-5555'); DELETE FROM Songs WHERE Title = 'Yesterday'; UPDATE Musicians SET Address = '456 Oak Ave, Anytown, USA' WHERE Name = 'John Smith'; SELECT Albums.AlbumID, Albums.Title, Albums.Copyright, Albums.Format FROM Albums WHERE Albums.ProducerID = (SELECT Musicians.MusicianID FROM Musicians WHERE Musicians.Name = 'John Smith'); SELECT Musicians.Name, Musicians.Address, Musicians.Phone FROM Musicians, Plays, Instruments WHERE Musicians.MusicianID = Plays.MusicianID AND Plays.InstrumentID = Instruments.InstrumentID AND Instruments.Name = 'guitar' ORDER BY Musicians.Name; SELECT Songs.Title, Songs.Author FROM Songs, AlbumSongs WHERE Songs.SongID = AlbumSongs.SongID AND AlbumSongs.AlbumID = (SELECT Albums.AlbumID FROM Albums WHERE Albums.Title = 'Abbey Road'); SELECT COUNT(*) FROM Albums WHERE YEAR(Albums.Copyright) = 2021; SELECT Musicians.Name FROM Musicians, Albums WHERE Musicians.MusicianID = Albums.ProducerID GROUP BY Musicians.Name HAVING COUNT(Albums.AlbumID) > (SELECT AVG(cnt) FROM (SELECT COUNT(*) as cnt FROM Albums GROUP BY ProducerID) as Producers); SELECT Musicians.Name FROM Musicians, Plays WHERE Musicians.MusicianID = Plays.MusicianID GROUP BY Musicians.Name HAVING COUNT(Plays.InstrumentID) > 2; SELECT Musicians.Name, COUNT(*) as TotalSongs FROM Musicians, Performs WHERE Musicians.MusicianID = Performs.MusicianID GROUP BY Musicians.Name;
I need the information turned into logical data.
Develop a logical data model based on the following requirements:
• Refinement of the conceptual model - including a refined Enhanced Entity-Relationship (EER) diagram.
• Derivation of relations from the refined conceptual model.
• Validation of logical model using normalization to BCNF.
• Validation of logical model against corresponding user transactions.
• Definition of integrity constraints including:
– Primary key constraints.
– Referential Integrity (foreign key) constraints.
– Entity integrity (NULL and default value) constraints.
– Alternate key constraints.
– General constraints if any
Relationship types:
- Plays: between Musician and Instrument, with participation constraints (0, N) on Musician and (0, N) on Instrument.
- AppearsOn: between Song and Musician, with participation constraints (1, N) on Song and (1, N) on Musician.
- Contains: between Album and Song, with participation constraints (1, N) on Album and (1, N) on Song.
- Produces: between Musician and Album, with participation constraints (1, N) on Musician and (1, 1) on Album.
- UsedIn: between Instrument and Song, with participation constraints (1, N) on Instrument and (1, N) on Song.
Attributes:
- Musician: SSN (primary key), name, address, phone number.
- Instrument: name (primary key), musical key.
- Album: album identifier (primary key), title, copyright date, format.
- Song: title (primary key), author.
- Producer: none.
Entity types:
- Musician
- Instrument
- Album
- Song
- Producer
Assumptions:
- A musician can only have one SSN.
- An instrument can only have one name.
- An album can only have one album identifier.
- A song's title is unique and cannot appear on more than one album.
- Each album must have exactly one producer.
Sample queries:
INSERT INTO Musicians (SSN, Name, Address, Phone)
VALUES ('123-45-6789', 'John Smith', '123 Main St, Anytown, USA', '555-555-5555');
DELETE FROM Songs
WHERE Title = 'Yesterday';
UPDATE Musicians
SET Address = '456 Oak Ave, Anytown, USA'
WHERE Name = 'John Smith';
SELECT Albums.AlbumID, Albums.Title, Albums.Copyright,
Albums.Format
FROM Albums
WHERE Albums.ProducerID = (SELECT Musicians.MusicianID
FROM Musicians
WHERE Musicians.Name = 'John Smith');
SELECT Musicians.Name, Musicians.Address, Musicians.Phone
FROM Musicians, Plays, Instruments
WHERE Musicians.MusicianID = Plays.MusicianID AND
Plays.InstrumentID = Instruments.InstrumentID AND
Instruments.Name = 'guitar'
ORDER BY Musicians.Name;
SELECT Songs.Title, Songs.Author
FROM Songs, AlbumSongs
WHERE Songs.SongID = AlbumSongs.SongID AND
AlbumSongs.AlbumID = (SELECT Albums.AlbumID
FROM Albums
WHERE Albums.Title = 'Abbey Road');
SELECT COUNT(*)
FROM Albums
WHERE YEAR(Albums.Copyright) = 2021;
SELECT Musicians.Name
FROM Musicians, Albums
WHERE Musicians.MusicianID = Albums.ProducerID
GROUP BY Musicians.Name
HAVING COUNT(Albums.AlbumID) > (SELECT AVG(cnt)
FROM (SELECT COUNT(*) as cnt
FROM Albums
GROUP BY ProducerID) as Producers);
SELECT Musicians.Name
FROM Musicians, Plays
WHERE Musicians.MusicianID = Plays.MusicianID
GROUP BY Musicians.Name
HAVING COUNT(Plays.InstrumentID) > 2;
SELECT Musicians.Name, COUNT(*) as TotalSongs
FROM Musicians, Performs
WHERE Musicians.MusicianID = Performs.MusicianID
GROUP BY Musicians.Name;
Step by step
Solved in 6 steps
