Question

A private kindergarten, Happy Learning, wants to build a database to manage their operations. You were assigned to create an initial design based on the following customer description of their operations.

There are 3 age levels in the kindergarten (turtles, foxes and monkeys). At each level there 3 classes (red, blue and green). For a class we keep a code based on the initial of group color and level (e.g. RT for red turtles, GF for green foxes). Each class has a room number and a phone number. Also, each class has a maximum capacity (between 25 and 31). Also, we will keep brief directions to the class location in the building.

For a student we will keep the name, any nickname, date of birth, address, and the class in which the student is currently enrolled. We will process students based on their first and last name, or nickname, if present. We will process the address based on the city, zipcode and street to identify recruiting areas.

For each student we will keep information about one or more parents (or caretakers). A parent may have several students enrolled in the kindergarted. For a parent (or caretaker) we will keep the name, home phone number, mobile phone number, work phone number, the personal email and relationship to the child (e.g. father, mother, grandfather). At least one type of phone is required, but some parents may not provide all types of phone numbers. Some parents do not provide emails. We will process parents based on their last name.

Each class has a teacher assigned. A teacher is assigned to a single class. For a teacher we keep the name, home phone number, work email, personal email, the graduated college and their major. All the teachers must have a bachelor degree. We will process teachers based on their first and last name.

Answer 1

Exp: First operation create Database

Create Database Kindergarten

Exp : Next operation create table

1 Create table turtles(Primarykey Room number int(),   Phone number int() website of Kindergarten location of class  )

2 Create table foxes(Primarykey  Room number int(), Phone number int() website of Kindergarten location of class  )

3 Create table monkeys(Primarykey Room number int(), Phone number int () website of Kindergartenlocation of class  )

Exp: Next operation Create table Subtables

Create table Red turtles( Primarykey Room number int(), Serial number int(), primarykey Firstname Varchar(), lastname Varchar(), nickname Varchar(), Date of birth dateyy/mm/dd, address Varchar(), City char(), ZipCode int(), Street location identify)

Create Table Green Foxes  ( Primarykey Room number int(), Serial number int(), primarykey Firstname Varchar(), lastname Varchar(), nickname Varchar(), Date of birth date yy/mm/dd, address Varchar(), City char(), ZipCode int(), Street location identify)

Create table Blue Monkeys ( Primarykey Room number int(), Serial number int(),primarykey Firstname Varchar(), lastname Varchar(), nickname Varchar(), Date of birth dateyy/mm/dd, address Varchar(), City char(), ZipCode int(), Street location identify)

Exp: Next operation create table teachers table and parents or caretaker table

Create Table Teachers(Serial number int(), Foreignkey Assigned Roomnumber int() firstname Varchar() lastname varchar(), phonenumber int(), email, education Qualification varchar())

Create table Parents

( foreignkey Student name Varchar() firstname Varchar(), Lastname Varchar(), Phonenumber int(), email , Guardan relation varchar())

Procedure

In Database the operation between tables defined by  Primarykey it describe the link of table and particular data information. For two or more table the operation link for foreignkey it describe attached table records to particular data information