Beginning Database Design- P24

Beginning Database Design- P24:This book focuses on the relational database model from a beginning perspective. The title is, therefore,Beginning Database Design. A database is a repository for data. In other words, you can store lots of informationin a database. A relational database is a special type of database using structures called tables.Tables are linked together using what are called relationships. You can build tables with relationshipsbetween those tables, not only to organize your data, but also to allow later retrieval of information fromthe database....
Nội dung trích xuất từ tài liệu:
Beginning Database Design- P24 Exercise Answers MUSICIAN_ID INTEGER FOREIGN KEY REFERENCES MUSICIAN WITH NULL, DATE DATE NOT NULL, TEXT MEMO NOT NULL ); The Microsoft Access MEMO datatype is used to represent very large strings.Exercise 2 solution CREATE TABLE INSTRUMENT ( INSTRUMENT_ID INTEGER PRIMARY KEY NOT NULL, SECTION_ID INTEGER FOREIGN KEY REFERENCES INSTRUMENT WITH NULL, INSTRUMENT CHAR VARYING(32) NOT NULL ); CREATE TABLE MUSICIAN ( MUSICIAN_ID INTEGER PRIMARY KEY NOT NULL, MUSICIAN CHAR VARYING(32) NOT NULL, PHONE CHAR VARYING(32) NULL, EMAIL CHAR VARYING(32) NULL ); CREATE TABLE GENRE ( GENRE_ID INTEGER PRIMARY KEY NOT NULL, PARENT_ID INTEGER FOREIGN KEY REFERENCES GENRE WITH NULL, GENRE CHAR VARYING(32) NOT NULL ); CREATE TABLE BAND ( BAND_ID INTEGER PRIMARY KEY NOT NULL, BAND CHAR VARYING(32) NOT NULL, FOUNDING_DATE DATE NOT NULL ); CREATE TABLE ADVERTISEMENT ( ADVERTISEMENT_ID INTEGER PRIMARY KEY NOT NULL, DATE DATE NOT NULL, TEXT MEMO NOT NULL ); CREATE TABLE DISCOGRAPHY ( DISCOGRAPHY_ID INTEGER PRIMARY KEY NOT NULL, CD_NAME CHAR VARYING(32) NOT NULL, RELEASE_DATE DATE NULL, PRICE MONEY NULL ); CREATE TABLE MERCHANDISE ( 433Appendix A MERCHANDISE_ID INTEGER PRIMARY KEY NOT NULL, TYPE CHAR VARYING(32) NOT NULL, PRICE MONEY NOT NULL ); CREATE TABLE SHOW_VENUE ( SHOW_ID INTEGER PRIMARY KEY NOT NULL, LOCATION CHAR VARYING(32) NOT NULL, ADDRESS_LINE_1 CHAR VARYING(32) NOT NULL, ADDRESS_LINE_2 CHAR VARYING(32) NULL, TOWN CHAR VARYING(32) NOT NULL, ZIP NUMBER(5) NULL, POSTAL_CODE CHAR VARYING(32) NULL, COUNTRY CHAR VARYING(32) NULL, DIRECTIONS MEMO NULL, PHONE CHAR VARYING(32) NULL SHOW_DATE DATE NOT NULL, SHOW_TIME CHAR VARYING(16) NOT NULL ); VENUE is changed to LOCATION CREATE TABLE FACT ( FACT_ID INTEGER NOT NULL, SHOW_ID INTEGER FOREIGN KEY REFERENCES SHOW WITH NULL, MUSICIAN_ID INTEGER FOREIGN KEY REFERENCES MUSICIAN WITH NULL, BAND_ID INTEGER FOREIGN KEY REFERENCES BAND WITH NULL, ADVERTISEMENT_ID INTEGER FOREIGN KEY REFERENCES ADVERTISEMENT WITH NULL, DISCOGRAPHY_ID INTEGER FOREIGN KEY REFERENCES DISCOGRAPHY WITH NULL, MERCHANDISE_ID INTEGER FOREIGN KEY REFERENCES MERCHANDISE WITH NULL, GENRE_ID INTEGER FOREIGN KEY REFERENCES GENRE WITH NULL, INSTRUMENT_ID INTEGER FOREIGN KEY REFERENCES INSTRUMENT WITH NULL, CD_SALE_AMOUNT MONEY NULL, MERCHANDISE_SALE_AMOUNT MONEY NULL, ADVERTISING_COST_AMOUNT MONEY NULL, SHOW_TICKET_SALES_AMOUNT_MONEY NULL );434 B Sample DatabasesThis appendix contains what should the most sensible versions of some of the more complete ERDdatabase model diagrams, as presented in this book. This appendix is intended merely as a refer-ence of database model ERDs.Following is a summary of the ERDs included in this appendix. ❑ Figure B-1 shows the book publication OLTP ERD. ❑ Figure B-2 shows the book publication reviews data warehouse ERD. ❑ Figure B-3 shows the book publication sales data warehouse ERD. ❑ Figure B-4 shows the musicians, bands, and advertisements OLTP ERD. ❑ Figure B-5 shows the musicians, bands, and advertisements data warehouse ERD. ❑ Figure B-6 shows the online auction house OLTP ERD. ❑ Figure B-7 shows the online auction house data warehouse ERD.Appendix B Author author_id Customer name customer_id customer CoAuthor address phone cuoauthor_id (FK) email publication_id (FK) Publisher credit_card_type ...