Granny-Joan曲奇.docx

数据库案例1——Granny Joan 曲奇
难度指数1
所需技能：
集合函数 查找向导
计算字段 关联
表单设计 报表设计
表单向导 报表向导
IIF函数 选择查询
表设计

案例背景
在过去的几年里，菲尔莫尔学校的学生向社区销售Granny Joan曲奇饼干，这给学校赚来了额外的资金。这些收入主要花在学生身上，用来增加教室设备、购买校服，或者购买操场锻炼器材。下个月，菲尔莫尔学校的学生将再次开始出售饼干。菲尔莫尔学校的主管尼利先生雇佣你来建立一个曲奇饼干销售系统的数据库。这个数据库用来记录可供出售的曲奇，销售曲奇的学生，以及获得的销售收入。要完成这个数据库，需要设计并填充4个表，建立这些表之间的关联，设计4个表单，几个报表，并创建一些查询。

案例情景
Granny Joan曲奇是非常走俏的包装精美的曲奇饼干，那些非盈利性的组织经常通过销售曲奇来为各种社区活动筹集经费。市场上能见到好几种曲奇饼干，有罐装的，也有礼品套装和篮子装的。菲尔莫尔学校从Granny Joan饼干公司购买曲奇饼干，然后提高一倍价格销售。
尽管去年的资金筹集取得了成功，尼利先生还是认为需要一个更有体系的追踪饼干销售情况的管理系统。通过使用一个数据库，尼利先生能够更准确地追踪饼干销售和利润情况。
尼利先生预想的销售过程如下：他的行政助理凯丽女士负责给学生发放曲奇饼干并收取学生销售这些饼干的收入。当一个学生愿意销售曲奇时，他去找凯丽女士请求领取饼干，凯丽女士把学生领取走的饼干数量输入数据库。当学生上缴收入时，将钱交给凯丽女士，由她更新数据库来记录学生上交的收入和学生已经销售的饼干数量。从今年开始，每个学生每天只能领取一次曲奇饼干，上交一次收入。
尼利先生雇用你设计一个饼干销售管理数据库。根据尼利先生的说明，这个数据库将存储学生信息、饼干信息、饼干发放信息、饼干销售信息等。你要建立曲奇数据库，需要创建销售员表单、曲奇表单、饼干发放表单和销售表单。同时，你要设计曲奇饼干日收入和累计总收入报表，建立表之间的关联，并创建一些查询。

存储说明
要建立这个数据库，首先你要创建4个表：曲奇表、销售员表、曲奇发放表和销售表（你的老师会提供给你必要的数据来填充这些表）。曲奇表存储可供销售的曲奇信息。表1展示了曲奇表的结构。CID字段由唯一的数字编码来代表这种曲奇，这个字段作为该表的主关键字。CookieName字段存储每种曲奇的名字。OurCost字段给出菲尔莫尔学校在向Granny Joan购买每份曲奇时支付成本。
表1 曲奇饼干表的结构
字段名称
数据类型
字段描述
字段大小
备注
CJD
数值型
唯一代码，确定一种饼干
长征性
必填项
CookieName
文本型
饼干名称
50
必填项
OurCost
数值型
说明在每包饼干上的花费
单精度型
必填项
使用货币格式
销售人员表存储进行饼干销售的学生的信息，表2展示了销售员表的结构。SellerID字段唯一确定一个学生并作为该表的主关键字。SellerFirstName和SellerLastName分别存储学生的姓和名。Grade字段确定学生的年级。
表2 销售员表结构
字段名称
数据类型
字段描述
字段大小
备注
SellerID
数值型
学生身份识别，作为主关键字
长整型
必填项
SellerFirstName
文本型
存储学生的姓氏
25
必填项
SellerLastName
文本型
存储学生的名字
50
必填项
Grade
数值型
存储学生的年级
字节型
必填项
由于学生会要求领取多种曲奇（罐装曲奇、礼品曲奇、蓝装曲奇），因此需要一个曲奇发放表来记录每个学生领取走的曲奇。当一个学生领取5个一磅罐装的奶油曲奇饼干和两个一磅罐装甜曲奇饼干时，就把这些数据输入曲奇发放表。
表3给出了曲奇发放表的结构。如图，该表一共有5个字段，依次是CheckOurID，CookieID，SellerID，CheckOurDate，和CheckOutQuantity。CheckOurID唯一确定一项纪录，作为该表的主关键字。销售表的ReferenceNo会使用该表的CheckOurID值，把销售收入和发放的曲奇对应起来。CookieID字段存储曲奇的编码。SellerID字段存储销售曲奇学生的编码，该字段用来确定领出曲奇的学生的身份。，CheckOurDate字段说明学生领取曲奇的时间，CheckOutQuantity字段记录学生领取了多少单位曲奇饼干。
表3 发放表的结构
字段名称
数据类型
字段描述
字段大小
备注
CheckOurID
自动编码型
发放交易的识别码，具有唯一性，作为主关键字
长整型
必填项
CookieID
数值型
识别饼干
长整型
必填项
SellerID
数值型
识别领取饼干的学生
长整型
必填项
CheckOurDate
日期/时间型
记录发放日期

必填项
CheckOutQuantity
数值型
记录发放数量
字节型
必填项
销售表记录每个学生销售曲奇的信息。表4给出了销售表的结构。该表6个字段，依次是SalesIdentificationNumber，ReferenceNo，SellerId，SaleDate，QuantitySold和AmountCheckedIn。其中，SalesIdentificationNumbe唯一确定销售表中的一个记录，这个字段的值有数据库管理系统分配。
表4 销售表结构
字段名称
数据类型
字段描述
字段大小
备注
SalesIdentificationNumber
自动编码型
数据库系统分配，作为主关键字
长整型
必填项
ReferenceNo
数值型
和发放表的CheckID字段对应
长整型
必填项
与学生当日领取的曲奇对应
SellerId
数值型
识别领取饼干的学生
长整型
必填项
SaleDate
日期型
记录学生上缴收入的日期

必填项
QuantitySold
数值型
记录学生的销量
字节型
必填项
AmountCheckedIn
数值型
汇总累计学生截至当天销售某种饼干上交的收入
单精度型
必填项
使用货币格式
当一个学生那里收到销售收入时，需要把收到的钱和曲奇发放表中的发放记录联系起来。一种有效的办法就是在该表中建立ReferenceNo字段，对每一项销记录，该字段存储一个CheckOutID的值来匹配曲奇发放表。SellerID字段确定销售曲奇的学生。当一个学生向凯丽女士上交销售收入时，日期存储到SaleDate字段。QuantitySold字段说明该学生销售的某种特定曲奇的数量。AmountCheckIn字段存储学生上交给凯丽女士的销售收入。（根据具体情况，你的老师可能会要求你把该字段设计为计算字段。因此，该字段也有可能不是必须的）
输入说明
尼利先生情急设计销售员表单、饼干表单、发放表单和销售表单。销售员表单使得尼利先生的助理凯丽女士能够很快输入销售曲奇的学生的数据。曲奇表单是得凯丽女士能够输入不同种类曲奇的数据。发放表单使得凯丽女士能够记录每个学生领取的曲奇饼干。当学生到凯丽女士的办公室上交销售收入时，凯丽女士将应用销售表单输入销售量和收入的数据。图1-4给出了这些表单的草图。

销售员
销售员身份代码：
销售员姓名：
年级：
图1销售员表单

饼干
饼干识别代码：
饼干包装名称：
成本：
图2 饼干表单

饼干发放
销售员身份代码：
销售员姓名：
年级：
发放序号
饼干编号
发放日期
发放数量

图3 发放表单

销售
销售员身份代码：
销售员姓名：
年级：
引用值
销售日期
销量
收入

图4 销售表单

信息说明
尼利先生需要饼干销售的日收入和销售总收入报表。图5和图6给出了这两个报表的草图（这些草图只起到说明作用，你的报表会有所不同）。尽管你可以自由设计这些报表，尼利先生要求报表有标准、专业的外观。
图5给出了饼干日销售收入报表的草图。尼利先生使用饼干销售日收入报表来观测饼干每天的销售情况，在该报表中，销售一栏说明学生销售了多少饼干以及收入是多少。
饼干日销售收入
（当前日期）
饼干名称 销量 销售收入（美元） 毛利润（美元）
A Lot of Chocolate Chip 11b Tin 2 10.00 5.00
.
.
Sweet Dreams Gift Set 5 1bs 4 104.00 52.00
图5 饼干日销售收入报表

尼利先生使用总收入报表来观测学校里资金筹集的目标有多远。该报表按字母顺序列出饼干的名称，对每种饼干给出截至当天的总销售量，总销售额和毛利润。图6给出了总收入报表的一个草图。
饼干累计收入
（当前日期）
饼干名称 销量 销售收入（美元） 毛利润（美元）
A Lot of Chocolate Chip 11b Tin 6 30.00 15.00
.
.
Sweet Dreams Gift Set 5 1bs 5 130.00 65.00
图6 累计总收入报表
尼利下生需要下列问题的答案，请建立查询来帮助尼利先生回答这些问题。如果愿意，你可以以这些问题为条件生产报表。
1. 按总销售额计，哪个年级销售的曲奇最多？找出该年级并得出总的收入。
2. 按总销售额计，哪个学生销售的曲奇饼干最多？
3. 按总销售额计，哪种曲奇对畅销？
4. 哪些学生卖完了他们领取的曲奇？
5. 哪些学生领取了曲奇，却没有上缴任何收入？
6. 尼利先生需要一份名单报表，这个报表能够方便他或凯丽女士查找学生领取的曲奇。该报表对每个学生按字母排序，提供每个学生的姓名、年级、领取的曲奇名称、领取日期和数量。
7. 尼利先生需要一个价格报表。该报表按字母顺序列出每种曲奇的名称、代码、成本、售价和总收入。
8. 尼利先生需要每个年级的曲奇发放报表。至少，报表要提供这个年级的学生姓名、学生领取的曲奇名称、数量，并按照学生姓氏字母排列这些数据。

实施要点
根据尼利先生的说明建立这个数据库时，你要常见一些查询，建立表之间的关联，设计表单和报表。其中一些选择查询要分类，制定指标，创建表达式，并且多个表的数据都会用到，这需要更加留意。当你建立关联时，要确保数据的指定完整性。尽管在设计表单和报表时你不受限制，但是每个表单和报表都应当统一、专业的外观。可以考虑使用表单和报表向导来准备初始表单和报表，然后再设计视图里编辑。
在这个数据库中至少有一个查询和表表会用到IIF的数据库查询函数，这个函数使你能够进行条件查询，例如查找空值字段。你可以通过系统的在线帮助来学习IIF函数的相关详细用法。

设计测试
在创建了所有的表单、表、关联、查询和报表之后，你应当测试你的数据库设计，请进行以下步骤。
1. 以下学生领取了曲奇，把它们的信息输入数据库。
销售员的名
销售员的姓
年级
Darrel
Jenel
6
Jared
Mounce
7
Mack
Fridley
4
2. 几个学生领取了曲奇，把下列信息输入数据库，假定领取日期为2006年10月6日。
销售员
饼干
数量
Darrel
A Lot of Chocolate Chip 11b Tin
Sugar Cookies 1lb Tin
Butter Cookies 1lb Tin
4
3
2
Jared Mounce
Butter Cookies 1lb Tin
Peppermint Cookies 1lb Tin
Cookies Gallore Basket 2.5lbs
2
1
2
Mack Fridley
Sweet Dreams Gift Set 5 1bs
A Lot of Chocolate Chip 11b Tin
2
11
3. 下列学生上交了收入，把这些信息输入数据库，假定上交收入的日期为2006年10月13日。
销售员
饼干
销量
上交数额（美元）
Darrel Jenel
A Lot of Chocolate Chip 11b Tin
2
10.00
Jared Mounce
Butter Cookies 1lb Tin
Peppermint Cookies 1lb Tin
Cookies Gallore Basket 2.5lbs
1
1
1
6.00
5.00
8.00
Mack Fridley
Sweet Dreams Gift Set 5 1bs
A Lot of Chocolate Chip 11b Tin
1
6
26.00
30.00
Micah Ernsberger
A Lot of Chocolate Chip 11b Tin
Butter Cookies 2lb Tin
Sugar Cookies 1lb Tin
2
4
2
10.00
24.00
10.00
Annette O’Mally
Sugar Cookies 2lb Tin
5
45.00
4. 尼利先生请你创建销售收入汇总报表，他要求按照销售日期升序排列，并对每个销售日，计算总收入。
5. 尼利先生要求你建立学生销售曲奇活动报表，汇总出每个学生领取的曲奇和销售业绩。在你设计报表时，该报表按字母顺序排列学生，并列出该学生领取的曲奇。对每种曲奇，给出领取的总数量，销售的总数量，领出而未销售的饼干数量。