西北工业大学数据库系统实验二

Book Entity (Strong)

 

                - Title (single valued, simple string) 

                - ISBN (single valued, simple alphanumeric string), pk 

                - Edition (single valued, simple numeric) 

                - Date of Publication (single valued, composite concatenation of  

                                       characters and numbers) 

                - Price (single valued, simple floating point number) 

                - Book Description (single valued, simple string) 

        Author Entity (Strong) 

                - Author Name 

                - Author_id, pk 

        Publisher Entity (Strong) 

                - Publisher id (single value, simple numeric), pk 

                - Publisher Name (single valued, simple string) 

                - Address (single valued, simple string) 

- together the publisher name and address could make an alternate key because no to publishers can have the same name and address 

        Customer Entity (strong) 

                - Customer_id (single valued, simple string), pk 

                - Name (composite  one value for first, middle and last name, 

                simple string) 

                - Mailing Address (single valued, simple string) 

                - Credit Card Number and Expiration Date (single value, simple  

                numeric sequence), alternate key 

                - Phone Number (single value, simple alphanumeric string) 

                - Email Address (single valued, simple alphanumeric string) 

        Shipment (strong) 

                - Date of Shipment ( single valued, composite of strings and  

                numbers)  

                - Tracking Number (single valued, simple alphanumeric string), pk 

                - Date of Expected Delivery ( single valued, compoite of strings 

                and numbers) 

        Order (Strong) 

                - Order Number (single valued, simple number), pk 

                - Mailing  Address (single value, simple string) 

                - Method of Shipment (single value, simple string) 

                - Date and Time of Order (when the order was placed)  

                - Total Price of the Order (multivalue, composite) 

        Promotion (strong entity type ) 

                - Promotion id number, pk 

                - Percentage Discount Points (single value, simple float) 

                - Duration of Promotion (start date and end date) ( 

                composite attributes like the dates above) 

        Line Item (weak entity type) 

- Total price for each book that is ordered (single value, two place precision float) 

                - Quantity of each item ordered 

        Category (strong entity) 

                - Category ID (single value, simple numeric), pk 

 - Category Name (single value, simple string)

（2）标识关系及其参与实体的作用、基数和多样性如下：

        - Book_Author(Author Write Book) 

          Roles: Book fulfills book role and author fulfills author role 

          Cardinality: M:N, many books have many authors 

          Participation: Book and Author are both total 

        - Book_category(Book BelongTo Category)

          Roles: book fulfills the book role and category fulfills the category role 

Cardinality: M:N, one book can belong to many categories,one category can contain many books 

          Participation: book is total, it must belong to some category, 

                          category is partial, a category can exist without 

                          any books in it. 

        - Publisher_Book (Publisher Publish Book)

          Roles: Book fulfills book or product role, Publisher fulfills 

                 publisher or producer role 

          Cardinality: 1:N, 1 publisher publishes many books 

          Participation: Publisher and book are total 

        - Customer_Order (Customer Has Order)

          Roles: Customer fulfills buyer role and order fulfills  

                bought product role 

          Cardinality: 1:N, 1 customer can have many orders 

          Participation: Customer is partial and order is total 

        - Book_Promotion (Promotion Promote Book)

          Roles: Book fulfills book or product role, Promotion 

                 fulfills sale role 

          Cardinality: M:N, many books can have many promotions, that is 

                       books can be part of more than 1 sale and each 

                      sale can have many books 

          Participation: Book is partial and promotion is total 

        - LineItem_Order (Order Contain LineItem)

          - Roles : line item fulfills the line item (one item per line in an order) 

                    and the order fulfills the order role. 

          - Cardinality: 1:N, one order contains many line items 

          - Participation: Both line item and order are total 

        - LineItem_Book (Book In LineItem)

          - Roles: Book fulfills the book role, line item fulfills the line item role 

          - Cardinality: 1:N, one book can be in many line items 

          - Participation: line item is total and book is partial 

        - Shipment_LineItem 

          - Roles: Partial order fulfills the partial order role and  

                   line item fulfills the line item role. 

          - Cardinality: 1:N, one partial order can have many line items 

          - Participation: partial order is total and line item is partial 

        - Isa_Category (Category Has Category)

          Roles: Category fulfills the subclass role and Supercategory 

                 fulfills the superclass role. 

           Cardinality: n:1, a super category can have many subcategories 

          Participation: Supercategory is total, there must be a supercatory for 

                    each subcategory, but supcategory is partial, not every supercategory must have a subcategory to exist.