Assume you have been given the following data model created for the Beauty Galle

Question

Assume you have been given the following data model created for the Beauty Gallery, a small art gallery that has been in business for 30 years. Review the conceptual data model carefully and follow the steps to transform the model into relational database design. Then implement the relational design into Access database. Submit the created Access database file.

Create each table and specify appropriate column data types, primary keys, foreign keys, and any special column characteristics in the Access database implementation.

Build relationships in the Relationship window in Access and specify referential integrity constraints & actions as appropriate.

WORK ARTIST CUSTOMER CustomerNumber WorkID ArtistlD PURCHASE LastName FirstName Address City State ZIP Phone Name Nationality Birthdate DeceasedDate CREATE Title Copy Description Phone IREFERRED-BY Has Interest In Admired_By

Explanation / Answer

/** customer table to store the customer info in DB **/

CREATE TABLE `customer` (
`CustomerNumber` int(10) NOT NULL,
`LastName` varchar(255) DEFAULT NULL,
`FirstName` varchar(255) DEFAULT NULL,
`Address` varchar(255) DEFAULT NULL,
`City` varchar(255) DEFAULT NULL,
`State` varchar(255) DEFAULT NULL,
`ZIP` varchar(255) DEFAULT NULL,
`Phone` varchar(255) DEFAULT NULL,
PRIMARY KEY (`CustomerNumber`)
) ENGINE=InnoDB;

/** Artist table to store artist info in DB **/
CREATE TABLE `artist` (
`ArtistID` int(10) NOT NULL,
`Name` varchar(255) DEFAULT NULL,
`Nationality` varchar(255) DEFAULT NULL,
`Birthdate` date DEFAULT NULL,
`DeceasedDate` date DEFAULT NULL,
PRIMARY KEY (`ArtistID`)
) ENGINE=InnoDB;

/** work table in which a foreing key relation maitained to map with Artist **/
CREATE TABLE `work` (
`WorkID` int(10) NOT NULL,
`fk_ArtistID` int(10) DEFAULT NULL,
`Title` varchar(255) DEFAULT NULL,
`Copy` varchar(255) DEFAULT NULL,
`Description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`WorkID`),
KEY `FK_ArtistID` (`fk_ArtistID`),
CONSTRAINT `FK_ArtistID` FOREIGN KEY (`fk_ArtistID`) REFERENCES `artist` (`ArtistID`)
) ENGINE=InnoDB;

/** Mapping table for customer_work , This table is to map the many to many relation between cutomer and work **/

CREATE TABLE `customer_work` (
`customer_work_id` int(10) NOT NULL,
`fk_CustomerNumber` int(10) DEFAULT NULL,
`fk_WorkID` int(10) DEFAULT NULL,
PRIMARY KEY (`customer_work_id`),
KEY `FK_CUSTOMERNAME` (`fk_CustomerNumber`),
KEY `FK_WORKID` (`fk_WorkID`),
CONSTRAINT `FK_CUSTOMERNAME` FOREIGN KEY (`fk_CustomerNumber`) REFERENCES `customer` (`CustomerNumber`),
CONSTRAINT `FK_WORKID` FOREIGN KEY (`fk_WorkID`) REFERENCES `work` (`WorkID`)
) ENGINE=InnoDB;

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.