Hatfield Apple Tree Suppliers
Hatfield Apple Tree Suppliers Hatfield Apple Tree Suppliers Hats Sell
Write out the Relational Schema for each of the 7 entities detailed. These should use the format as defined in this example (not taken from the specification) underlining the primary key attributes and marking the foreign key attributes with an asterisk. Employee ( Employee_Number , Name, Address, Department* ). Use the same names for the Relational Schema statements as are specified in the E-R Diagram. Do NOT add or remove any attributes, specifically, do NOT replace composite Primary Keys with a Simple Primary Key.
Using your Oracle account, create the table structures as reflected in the diagram and your Relational Schema. Again, use the same names for the CREATE TABLE statements as are specified in the E-R Diagram these should not clash with any existing tables you have created so far, however, if you have created your own tables with the same name, rename them prior to starting this task. Ensure that you create and code the following: All attributes, labelled exactly as specified in the E-R diagram. Suitable datatypes with sensible sizes and lengths, and reflecting the nominal or quantitative nature of the attribute. All PRIMARY KEY and FOREIGN KEY constraints. Use NOT NULL constraints where appropriate. Provide CHECK constraints where appropriate. There are no UNIQUE constraints expected. Figure 1 – Finalised E-R Diagram. Submission Requirements: Upload a PDF document to CANVAS, it should include:
- A title line including “Assignment 2” followed by your ORACLE USERNAME.
- Your 7 Relational Schema statements.
- Screenshots of the CREATE TABLE SQL commands you used to create the final version of each of the 7 tables.
Paper For Above instruction
The process of designing and implementing a relational database schema for Hatfield Apple Tree Suppliers (HATS) involves several critical steps, including clearly defining entities and their attributes, establishing relationships, and translating these into relational schemas and SQL table structures. This essay aims to articulate a comprehensive approach to this task, demonstrating the understanding of relational database principles and practical SQL implementation aligned with the provided business context.
Entity Identification and Attribute Specification
The first step involves identifying the core entities essential to the HATS business model. The key entities include:
- Tree Batch: representing batches of grafted trees, characterized by batch ID, graft date, variety, and rootstock used.
- Variety: encompassing different apple varieties with attributes such as name, pollination group, fruiting season, suitability for cooking or eating, fruit size, color, and yield.
- Rootstock: detailing rootstock types, their height characteristics, and descriptions.
- Customer: including customer registration details such as customer ID, name, address, postcode, phone number, and registration date.
- Order: recording order ID, order date, time, and customer ID.
- Order Details: itemizing the quantity of trees ordered from each batch.
- Tree Batch Inventory: tracking the stock quantities of each batch.
Each entity's attributes are determined based on the description, ensuring all relevant data points are captured to facilitate business operations and data management.
Relational Schema Development
Based on the entities, the relational schemas specify primary keys (underlined) and foreign keys (marked with an asterisk). For example:
- TreeBatch: (BatchID, GraftDate, VarietyName, RootstockType)
- Variety: (Name, PollinationGroup, FruitingSeason, IsCooking, IsEating, FruitSize, Color, Yield)
- Rootstock: (Type, Description, Height)
- Customer: (CustomerID, Name, Address, Postcode, Phone, RegistrationDate)
- Order: (OrderID, OrderDate, OrderTime, CustomerID*)
- OrderDetails: (OrderID, BatchID, Quantity)
- Stock: (BatchID*, Quantity)
This schema ensures referential integrity and supports the one-to-many relationships between entities, such as customer- orders and batch-orders.
SQL Table Creation and Constraints
The next phase involves translating the relational schemas into SQL CREATE TABLE statements using Oracle syntax. Each table creation statement incorporates appropriate data types—such as VARCHAR2 for textual data, NUMBER for numerical fields, and DATE or TIMESTAMP for date/time attributes. Constraints include:
- Primary key constraints to uniquely identify records.
- Foreign key constraints to enforce referential integrity across tables.
- NOT NULL constraints to prevent incomplete data entries.
- CHECK constraints to restrict attribute values, such as fruit size or yield.
For instance, the creation of the TreeBatch table would resemble:
CREATE TABLE TreeBatch (
BatchID NUMBER PRIMARY KEY,
GraftDate DATE NOT NULL,
VarietyName VARCHAR2(50) NOT NULL,
RootstockType VARCHAR2(10) NOT NULL,
CONSTRAINT fk_variety FOREIGN KEY (VarietyName) REFERENCES Variety(Name),
CONSTRAINT fk_rootstock FOREIGN KEY (RootstockType) REFERENCES Rootstock(Type)
);
Similar scripts are crafted for the remaining entities, ensuring all schema constraints satisfy business rules and data integrity requirements.
Implementation and Validation
Post schema creation, the process involves validating table structures by executing the SQL scripts within the Oracle environment, inspecting for syntax errors, and verifying constraints. Screenshots of commands serve as documentation and evidence of successful implementation. Data can then be inserted to test the schema's functionality, especially focusing on referential integrity constraints and data validation mechanisms.
Conclusion
The development of relational schemas and SQL table structures for HATS embodies a systematic approach grounded in sound database design principles. Carefully defining entities, attributes, relationships, and constraints ensures that the database will robustly support the company's operations. Proper execution of the CREATE TABLE commands, along with thorough testing, will establish a reliable and scalable database foundation, crucial for efficient business management and future growth.
References
- Communications of the ACM, 13(6), 377–387.