1. Create a relational DB for the pizza Database, with at least 3 (three) record

Question

1. Create a relational DB for the pizza Database, with at least 3 (three) records per relational table.
2. Create a hierarchical (tree) semi-structured representation of the DB.
3. Create an XML representation of the DB.
4. Support the XML representation with an XML DTD specification.
5. Support the XML representation with an XML Schema specification.
6. Create a JSON representation of the DB

Appendix
1. Pizza Delivery Shop DB design. Relational schemas.
Customer(CustomerPhoneKey [PK], LastName, FirstName, Address1, Address2, City, State, Zip).
CustomerOrder(OrderKey [PK], OrderDate, OrderTime, CustomerPhoneKey [FK], EmployeeKey[FK]).
Employee(EmployeeKey [PK], LastName, FirstName, HireDate).
Product(ProductKey [PK], ProductName, UnitSize, UnitPrice).
OrderDetail(OrderDetailKey [PK], OrderKey [FK], ProductKey [FK], OrderDetailQuantity, OrderDetailPriceCharged).

Explanation / Answer

1. Create a relational DB for the pizza Database, with at least 3 (three) records per relational table.   

Customer Table

CustomerPhoneKey[PK]

LastName

FirstName

Address1

Address2

City

State

Zip

100

Mary

Jones

1444 S.

Alameda street

LosAngeles

California

900021

101

Jacks

Peter

1444 S.

Alameda street

LosAngeles

California

900021

102

Pracks

Harry

1444 S.

Alameda street

LosAngeles

California

900021

                                                CustomerOrder Table

OrderKey [PK]

OrderDate

OrderTime

CustomerPhoneKey [FK]

EmployeeKey[FK]

200

12-05-17

12PM

100

300

201

20-07-17

10PM

101

301

202

03-05-17

5PM

102

302

                                                                Employee Table

EmployeeKey [PK]

LastName

FirstName

HireDate

300

Smith

Jacob

12-03-16

301

Jones

Daniel

02-04-17

302

Williams

Ethan

23-06-16

                                               Product Table

ProductKey [PK]

ProductName

UnitSize

UnitPrice

400

Italian Pizza

Medium

8.6$

401

Cheese chicken Pizza

Large

10$

402

Corn Pizza

Medium

5.5$

OrderDetail Table

OrderDetailKey [PK]

OrderKey [FK]

ProductKey [FK]

OrderDetailQuantity

OrderDetailPriceCharged

500

200

400

3

9$

501

201

401

1

11.5$

502

202

402

2

6.5$

2. Create a hierarchical (tree) semi-structured representation of the DB.

3. Create an XML representation of the DB.   

<!doctype name "PizzaShopXML">
<Customer>
<Customer1>
   <CustomerPhoneKey>100</CustomerPhoneKey>
   <LastName>Mary</LastName>
   <FirstName>Jones</FirstName>
   <Address1>1444 S.</Address1>
   <Address2>Alameda street</Address2>
   <City>LosAngeles</City>
   <State>California</State>
   <Zip>900021</Zip>
</Customer1>
</Customer>
<CustomerOrder>
<CustomerOrder1>
   <OrderKey>200</OrderKey>
   <OrderDate>12-05-17</OrderDate>
   <OrderTime>12PM</OrderTime>
   <CustomerPhoneKey >100</CustomerPhoneKey >
   <EmployeeKey>300</EmployeeKey>
</CustomerOrder1>
</CustomerOrder>
<Employee>
<Employee1>
   <EmployeeKey >300</EmployeeKey >
   <<LastName>Smith</LastName>
   <FirstName>Jacob</FirstName>
   <HireDate>12-03-16</HireDate>
</Employee1>
</Employee>
<Product>
<Product1>
   <ProductKey>400</ProductKey>
   <<ProductName>Italian Pizza</ProductName>
   <UnitSize>Medium</UnitSize>
   <UnitPrice>8.6$</UnitPrice>
</Product1>
</Product>
<OrderDetail>
<OrderDetail1>
   <OrderDetailKey >500</OrderDetailKey >
   <OrderKey >200</OrderKey >
   <ProductKey >400</ProductKey >
   <OrderDetailQuantity >3</OrderDetailQuantity >
   <OrderDetailPriceCharged>9$</OrderDetailPriceCharged>
</OrderDetail1>
</OrderDetail>

4. Support the XML representation with an XML DTD specification.

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified">
<xs:element name="PizzaDeliveryShopDetails">
<xs:complexType>
<xs:sequence> <xs:element name="Customer">
<xs:complexType> <xs:sequence> <xs:element name="Customer1">
<xs:complexType>
<xs:sequence>
<xs:element type="xs:byte" name="CustomerPhoneKey"/>
<xs:element type="xs:string" name="LastName"/>
<xs:element type="xs:string" name="FirstName"/>
<xs:element type="xs:string" name="Address1"/>
<xs:element type="xs:string" name="Address2"/>
<xs:element type="xs:string" name="City"/>
<xs:element type="xs:string" name="State"/>
<xs:element type="xs:int" name="Zip"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="CustomerOrder">
<xs:complexType>
<xs:sequence>
<xs:element name="CustomerOrder1">
<xs:complexType>
<xs:sequence>
<xs:element type="xs:short" name="OrderKey"/>
<xs:element type="xs:string" name="OrderDate"/>
<xs:element type="xs:string" name="OrderTime"/>
<xs:element type="xs:byte" name="CustomerPhoneKey"/>
<xs:element type="xs:short" name="EmployeeKey"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Employee">
<xs:complexType>
<xs:sequence>
<xs:element name="Employee1">
<xs:complexType>
<xs:sequence>
<xs:element type="xs:short" name="EmployeeKey"/>
<xs:element type="xs:string" name="LastName"/>
<xs:element type="xs:string" name="FirstName"/>
<xs:element type="xs:string" name="HireDate"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Product">
<xs:complexType>
<xs:sequence>
<xs:element name="Product1">
<xs:complexType>
<xs:sequence>
<xs:element type="xs:short" name="ProductKey"/>
<xs:element type="xs:string" name="ProductName"/>
<xs:element type="xs:string" name="UnitSize"/>
<xs:element type="xs:string" name="UnitPrice"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="OrderDetail">
<xs:complexType>
<xs:sequence>
<xs:element name="OrderDetail1">
<xs:complexType>
<xs:sequence>
<xs:element type="xs:short" name="OrderDetailKey"/>
<xs:element type="xs:short" name="OrderKey"/>
<xs:element type="xs:short" name="ProductKey"/>
<xs:element type="xs:byte" name="OrderDetailQuantity"/>
<xs:element type="xs:string" name="OrderDetailPriceCharged"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

6. Create a JSON representation of the DB

{

   "Customer": {

      "Customer1": {

         "CustomerPhoneKey": "100",

         "LastName": "Mary",

         "FirstName": "Jones",

         "Address1": "1444 S.",

         "Address2": "Alameda street",

         "City": "LosAngeles",

         "State": "California",

         "Zip": "900021"

      }

},

   "CustomerOrder": {

      "CustomerOrder1": {

         "OrderKey": "200",

         "OrderDate": "12-05-17",

         "OrderTime": "12PM",

         "CustomerPhoneKey": "100",

         "EmployeeKey": "300"

      }

   },

   "Employee": {

      "Employee1": {

         "EmployeeKey": "300",

         "LastName": "Smith",

         "FirstName": "Jacob",

         "HireDate": "12-03-16"

      }

   },

   "Product": {

      "Product1": {

         "ProductKey": "400",

         "ProductName": "Italian Pizza",

         "UnitSize": "Medium",

         "UnitPrice": "8.6$"

      }

   },

   "OrderDetail": {

      "OrderDetail1": {

         "OrderDetailKey": "500",

         "OrderKey": "200",

         "ProductKey": "400",

         "OrderDetailQuantity": "3",

         "OrderDetailPriceCharged": "9$"

      }

   }

}

  

CustomerPhoneKey[PK]

LastName

FirstName

Address1

Address2

City

State

Zip

100

Mary

Jones

1444 S.

Alameda street

LosAngeles

California

900021

101

Jacks

Peter

1444 S.

Alameda street

LosAngeles

California

900021

102

Pracks

Harry

1444 S.

Alameda street

LosAngeles

California

900021

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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